MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Form problem

  Asked By: Adelisa    Date: Nov 13    Category: MS Office    Views: 586

I am using an user form with combo lists to enter data in a worksheet
called EnterCowData. Everything is working except cmdEnterData_Click().
The first value CowListStart.Offset(j, 8).Value = TxtAIPregDate.Text
is entered on the worksheet not the other two from the SaveRow().

Option Explicit
Dim FirstAIDate As Variant
Dim SecondAIDate As Variant
Dim ThirdAIDate As Variant
Dim CmyLastRow As Variant
Dim CmyLastARow As Variant
Dim CmyRange As Variant
Dim CowId As Integer
Dim CowListStart As Variant
Dim i, j As Integer
Dim CowStart As Range
Dim p As Integer
Dim AIPregRange As String
Dim PregRange As String
Dim PalpPregRange As String
Dim BreedRange As String

Private Sub UserForm_initialize()

'pregnant yes or no
cboPregnancyTest.List = Array("Yes", "No")

'List of breedings
cboBreedingNo.List = Array(1, 2, 3, 4, 5)

' I would like to have all the rows read
'for combo box

FirstAIDate = txt1AIDate.Value
SecondAIDate = txt2AIDate.Value
ThirdAIDate = txt3AIDate.Value


CmyLastRow = LastCell(Worksheets("EnterCowData")).Address
CmyRange = "A3:" & CmyLastRow

'sort the range by CowID
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

Worksheets("EnterCowData").Range(CmyRange).Name = "Options"
cboCowList.RowSource = "Options"
cboCowList.BoundColumn = 1
cboCowList.ColumnCount = 1

Application.ScreenUpdating = True

End Sub

Private Sub cboCowList_Click()

CmyLastARow = LastCell(Worksheets("EnterCowData")).Row
CmyRange = "A3: A" & CmyLastARow
Set CowListStart = Worksheets("EnterCowData").Range("A3")
CowId = cboCowList.Value
i = 0
Do Until i = CmyLastARow + 1
If CowListStart.Offset(i, 0).Value = CowId Then

j = i

End If
i = i + 1

End Sub

'function to call values
Private Sub LoadRow()
txt1AIDate.Text = CowListStart.Offset(i, 3).Value
txt2AIDate.Text = CowListStart.Offset(i, 4).Value
txt3AIDate.Text = CowListStart.Offset(i, 5).Value
txt4AIDate.Text = CowListStart.Offset(i, 6).Value
txt5AIDate.Text = CowListStart.Offset(i, 7).Value
TxtAIPregDate.Text = CowListStart.Offset(i, 8).Value
TxtPregDate.Text = CowListStart.Offset(i, 9).Value
cboPregnancyTest.Text = CowListStart.Offset(i, 10).Value
cboBreedingNo.Text = CowListStart.Offset(i, 11).Value
End Sub

Private Sub cmdEnterData_Click()

Call SaveRow

' Set focus to Name textbox:
End Sub

'function to save values
Private Sub SaveRow()
CowListStart.Offset(j, 8).Value = TxtAIPregDate.Text
CowListStart.Offset(j, 9).Value = TxtPregDate.Text
CowListStart.Offset(j, 10).Value = cboPregnancyTest.Text
CowListStart.Offset(j, 11).Value = cboBreedingNo.Text
End Sub

Private Sub cmdClose_Click()
'Save form contents before changing rows:
Unload Me ' Close the form
Call ClearDatabase
Call FunctionDatabase
Call AIDates 'fill AI Dates on EnterCowData sheet
End Sub



2 Answers Found

Answer #1    Answered By: Scott Anderson     Answered On: Nov 13

Check that all the fields do actually exist and are spelled in exactly this way.
If there is a crash in the Click handler, processing will probably just stop,
without any visible message. When I look at the help for combo  box, it implies
that you can't read the "text" property, only set  it. Not sure about this
though. Should you be using the "value" property?

Answer #2    Answered By: Abaddon Cohen     Answered On: Nov 13

I think it is a scoping problem  and the variaables you are using are undefined
at this point.

To check this I would put a break point on the line

CowListStart.Offset(j, 8).Value = TxtAIPregDate.Text

and then pass the mouse over each of the variables you propose to save. I
suspect they will have no value.

Didn't find what you were looking for? Find more on Form problem Or get search suggestion and latest updates.