Problem with User form VBA

[mayou]

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

 

[Gary Keramidas]

adapt something like this partial code. this would put the input data into the
last row in column A

dim lastrow as long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & lastRow).Value = Me.TBNum.Value

 

[Ardus Petus]

If Feuil1.Cells(5, 1).Value = "" Then
currentrow = 5 ' (list is empty - start in row 5
Else
currentrow = Feuil1.Cells(Rows.Count, 1).End(xlUp).Row + 1
End If

HTH

 

[mayou]

Thank you for giving me some indications of what could be wrong. I
solved the problem with the ADD Command button, but now the DELETE
command button does not work. Please could you give me some advice?
Here are the codes of the form:

'Since we will be allowing the user to move up and down the list,
'we’ll 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
Dim EnrollDate As Date
Dim FirstEmptyRow 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(3, 1).Value = "" Then
CurrentRow = 3 ' (list is empty - start in row 3
Else
'first empty cell in column A
FirstEmptyRow = Sheet1.Columns("A:A").Find(What:="",
LookAt:=xlWhole).Row
CurrentRow = FirstEmptyRow
End If
' Clear the form for user to add new name:
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 cmdNext_Click()
'Save form contents before changing rows:
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 > 3 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 3:
CurrentRow = 3
txtEnrollDate.Value = EnrollDate
LoadRow
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:
Rows(CurrentRow).Delete
' Show contents of new current row in the form:
LoadRow
End If

End Sub

'function to call values
Private Sub LoadRow()
txtCowID.Text = Sheet1.Cells(CurrentRow, 1).Value
txtEnrollDate.Text = Sheet1.Cells(CurrentRow, 2).Value
End Sub

'function to save values
Private Sub SaveRow()
Sheet1.Cells(CurrentRow, 1).Value = txtCowID.Text
Sheet1.Cells(CurrentRow, 2).Value = txtEnrollDate.Text

End Sub