Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problem with user for in Excel

  Asked By: Kuhaylah    Date: Mar 26    Category: MS Office    Views: 1149
  

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’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

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

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Kara Hicks     Answered On: Mar 26

I have created a userform  with all the command buttons and 7 textboxes and I
tried your code  (roughtly). I did not encountered any problems with the cmdAdd.
What is it that is not working? could you give more details.

 
Answer #2    Answered By: Jens Brown     Answered On: Mar 26

When you say it doesn't work, what do you mean. Precisely what happens when you
click it?

 
Answer #3    Answered By: Darrel Nelson     Answered On: Mar 26

I think I understand what your problem  is but I am not enough acquainted with
Excell vba  to be able to really help  you.
I can only point you to a direction.
.
If I understand you correctly, you want to find out what is the last row  with
data in your sheet and add  a new row from there.
You are using the used range.rows.count to give you the number  of used rows.
For some reason you are beginning your currentRow as number 5.
But what happens if the first 4 rows  are empty... Then your usedRange.rows
count will begin from row5 to ...
So first of all you should check whether row4 is empty  and not row5
If Sheet1.Cells(4, 1).Value = "" Then
but then again what happens if row4 is empty but not row 3 or 2 or 1.

 
Didn't find what you were looking for? Find more on Problem with user for in Excel Or get search suggestion and latest updates.




Tagged: