I narrowed down my problem in using the form with a new workbook.
If I use the add button to add a value, it will always put the value in
row 5.
If I click the next button, then the add button, the next value will be
put on row 6 and so on.
How can I fix this problem so the user can add a value without going to
the next row?

It had to do with UsedRange .Rows.Count?

I have a worksheet called sheet1 that receives data through the user
form with the following codes:

'Since we will be allowing the user to move up and down the list,
'we?Tll need a way to keep track of which row number the form is
'currently displaying.
'We will do this by creating a "form level" variable.
Dim CurrentRow As Long

Private Sub cmdAdd_Click()
'Save form contents before changing rows
SaveRow
' Set current row to first empty row, i.e. one row after
' the last row currently in use:
If Sheet1.Cells(5, 1).Value = "" Then
CurrentRow = 5 ' (list is empty - start in row 5
Else
CurrentRow = Sheet1.UsedRange.Rows.Count + 1
End If
' Clear the form for user to add new name:
Call LoadRow
' Set focus to Name textbox:
txtCowID.SetFocus
End Sub

Private Sub cmdClose_Click()
'Save form contents before changing rows:
SaveRow
Unload Me ' Close the form
End Sub

Private Sub cmdDelete_Click()
Dim smessage As String
smessage = "Are you sure you want to delete Cow" + txtCowID.Text
+ "?"
If MsgBox(smessage, vbQuestion + vbYesNo, _
"Confirm Delete") = vbYes Then
' Delete current row:
Sheet1.Rows(CurrentRow).Delete
' Show contents of new current row in the form:
LoadRow
End If

End Sub

Private Sub cmdNext_Click()
'Save form contents before changing rows:
Call SaveRow
' Increment row number:
CurrentRow = CurrentRow + 1
'Show contents of new row in the form:
LoadRow
End Sub

Private Sub cmdPrevious_Click()
' Show previous only if not already in first row:
If CurrentRow 5 Then
'Save form contents before changing rows:
SaveRow

' Decrement row number:
CurrentRow = CurrentRow - 1

' Show contents of new row in the form:
LoadRow
End If
End Sub

Private Sub UserForm_Activate()
' Read initial values from Row 5:
CurrentRow = 5
LoadRow
End Sub
'function to call values
Private Sub LoadRow()
txtCowID.Text = Sheet1.Cells(CurrentRow, 1).Value
txtEnrollDate.Text = Sheet1.Cells(CurrentRow, 2).Value
txt1AIDate.Text = Sheet1.Cells(CurrentRow, 3).Value
txt2AIDate.Text = Sheet1.Cells(CurrentRow, 4).Value
txt3AIDate.Text = Sheet1.Cells(CurrentRow, 5).Value
txt4AIDate.Text = Sheet1.Cells(CurrentRow, 6).Value
txt5AIDate.Text = Sheet1.Cells(CurrentRow, 7).Value
End Sub
'function to save values
Private Sub SaveRow()
Sheet1.Cells(CurrentRow, 1).Value = txtCowID.Text
Sheet1.Cells(CurrentRow, 2).Value = txtEnrollDate.Text
Sheet1.Cells(CurrentRow, 3).Value = txt1AIDate.Text
Sheet1.Cells(CurrentRow, 4).Value = txt2AIDate.Text
Sheet1.Cells(CurrentRow, 5).Value = txt3AIDate.Text
Sheet1.Cells(CurrentRow, 6).Value = txt4AIDate.Text
Sheet1.Cells(CurrentRow, 7).Value = txt5AIDate.Text

End Sub

Everything works except the add button. I would appreciate if you
could look the code over and help me.
I am new at VBA.
Thank you very much


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