Hey there,
I'm trying to create a macro for a user form so when the user fills in customer information and clicks submit, it will add a customer name and time stamp to a "customer list" table.
There will eventually be more than the customer name and time, once I get the rest of the code working properly.
It is also set to add a row to the table before adding the customer, so I never run out of rows. Not sure if that has anything to do with my issue.
Every time I run it, it adds the customer to the bottom row (the new row that it added) and still leaves empty rows above it.
I suppose it doesn't have to add a row if there is already an open row available, but the main issue is the customer should always be added to the first empty row, not the very last row.
I prefer to not upload the entire spreadsheet because there is some confidential info on it, but here is what I can share...
I attached a screenshot to that portion of the sheet. The code should've inserted Customer 2 into row 17 as it is the first empty row, but instead, it inserted them into row 20, the last empty row.
Here is the code I have right now for when the user clicks submit on the user form:
Private Sub Submit_Button_Click()
'Unprotects sheet while code runs*************
Dim pswStr As String
pswStr = "1234"
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=pswStr
' Add-row code starts here************
Dim CurrentSheet As Worksheet
Dim TableListObject As ListObject
Dim TableObjectRow As ListRow
'Adds a row
Set CurrentSheet = Sheets("Daily Goal and Sale Tracker")
Set TableListObject = CurrentSheet.ListObjects(1)
Set TableObjectRow = TableListObject.ListRows.Add
'User input form code starts here***************
'Selects next open row (the added row)
Dim NextRow As Long
NextRow = Worksheets("Daily Goal and Sale Tracker").Range("B" & Rows.Count).End(xlUp).row
With Worksheets("Daily Goal and Sale Tracker")
'Adds time stamp
.Range("A" & NextRow).Value = Now
'Adds Customer Name
.Range("B" & NextRow).Value = Me.Name_Text.Value
End With
'Resets form controls after submitting
Me.Name_Text.Value = ""
'Protects sheet after input code runs***************
ActiveSheet.Protect Password:=pswStr, DrawingObjects:=False, _
Contents:=True, Scenarios:=False, _
AllowFormattingCells:=False, AllowFormattingColumns:=False, _
AllowFormattingRows:=False, AllowInsertingColumns:=False, _
AllowInsertingRows:=False, AllowInsertingHyperlinks:=False, _
AllowDeletingColumns:=False, AllowDeletingRows:=False, _
AllowSorting:=False, AllowFiltering:=True, _
AllowUsingPivotTables:=False
Application.EnableEvents = True
Application.ScreenUpdating = True
'Shows confirmation MsgBox
MsgBox "Customer Added Successfully!"
End Sub
I appreciate any info you can share!
Bookmarks