Form problem
 

I have an user form with combo lists that enter data into a worksheet
called EnterCowData. It is working fine except the command button
cmdEnterData_Click(), only the first value ( CowListStart.Offset(j,
8).Value = TxtAIPregDate.Text) is entered on the worksheet, not the
other two. I cannot figure out what is wrong. If I change the order the
first one is the one recognized. Please could you help me?


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

Worksheets("EnterCowData").Activate

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

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


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

Application.ScreenUpdating = True

End Sub

Private Sub cboCowList_Click()

Worksheets("EnterCowData").Activate
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
LoadRow

j = i

End If
i = i + 1
Loop


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:
cboCowList.SetFocus
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

Thank you


--
mayou
------------------------------------------------------------------------
mayou's Profile: http://www.excelforum.com/member.php...o&userid=33359
View this thread: http://www.excelforum.com/showthread...hreadid=543377