apocalypticdon
New Member
- Joined
- Oct 4, 2014
- Messages
- 2
im using the vba to make a userform to help father in law manage data in his personal business. he is still learning to use this stuff so im trying to make it as easy as possible for him. I have found the code:
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
If Trim(Me.TextBox_name.Value) = "" Then
Me.TextBox_name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox_number.Value
ws.Cells(iRow, 2).Value = Me.TextBox_name.Value
ws.Cells(iRow, 3).Value = Me.TextBox_date.Value
ws.Cells(iRow, 4).Value = Me.TextBox_owed.Value
ws.Cells(iRow, 5).Value = Me.TextBox_paid.Value
MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.TextBox_number.Value = ""
Me.TextBox_name.Value = ""
Me.TextBox_owed.Value = ""
Me.TextBox_paid.Value = ""
Me.TextBox_name.SetFocus
End Sub
that adds data to the next line and works good. but his list is what people owe him, so say a person pays that is like 2nd on list of 10 people, he will delete that person and there is a blank space in the list. the code will not see that space and will add it to the bottom. I would like to get the code to fill in any line open first to keep the list organized and compact. any help will be greatly apperciated
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
If Trim(Me.TextBox_name.Value) = "" Then
Me.TextBox_name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox_number.Value
ws.Cells(iRow, 2).Value = Me.TextBox_name.Value
ws.Cells(iRow, 3).Value = Me.TextBox_date.Value
ws.Cells(iRow, 4).Value = Me.TextBox_owed.Value
ws.Cells(iRow, 5).Value = Me.TextBox_paid.Value
MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.TextBox_number.Value = ""
Me.TextBox_name.Value = ""
Me.TextBox_owed.Value = ""
Me.TextBox_paid.Value = ""
Me.TextBox_name.SetFocus
End Sub
that adds data to the next line and works good. but his list is what people owe him, so say a person pays that is like 2nd on list of 10 people, he will delete that person and there is a blank space in the list. the code will not see that space and will add it to the bottom. I would like to get the code to fill in any line open first to keep the list organized and compact. any help will be greatly apperciated