Good Morning!
Can anyone help me to understand what I would need to do to modify the below code so that It is looking for the next empty cell in Column A instead of the next Empty row? Because I have formulas in every row other columns, the next entry is going to the very end of the sheet instead of the next blank row. This is upon hitting a submit button in a userform. THank you.
Code:
PHP Code:
Private Sub ListBox3_Click()
End Sub
Private Sub CommandButton1_Click()
Dim WS_Data As Worksheet
Dim LastRow As Long
Dim NextRow As Long
Set WS_Data = Worksheets("Customers")
'find last row
LastRow = WS_Data.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
NextRow = LastRow + 1
'Enter data to "Data" sheet
WS_Data.Range("A" & NextRow) = TextBox1.Value
WS_Data.Range("B" & NextRow) = TextBox2.Value
WS_Data.Range("C" & NextRow) = TextBox3.Value
WS_Data.Range("D" & NextRow) = TextBox4.Value
WS_Data.Range("E" & NextRow) = TextBox5.Value
WS_Data.Range("F" & NextRow) = ComboBox1.Value
WS_Data.Range("G" & NextRow) = ComboBox2.Value
WS_Data.Range("H" & NextRow) = ComboBox3.Value
WS_Data.Range("I" & NextRow) = TextBox6.Value
WS_Data.Range("J" & NextRow) = TextBox7.Value
WS_Data.Range("K" & NextRow) = TextBox8.Value
WS_Data.Range("L" & NextRow) = ComboBox4.Value
WS_Data.Range("M" & NextRow) = ComboBox5.Value
'Clear the textboxes
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
ComboBox1.Value = ""
ComboBox2.Value = ""
ComboBox3.Value = ""
ComboBox4.Value = ""
ComboBox5.Value = ""
'Save workbook
ActiveWorkbook.Save
UserForm1.Hide
End Sub
Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "Atlantic"
.AddItem "Berks"
.AddItem "Cape May"
.AddItem "Camden"
.AddItem "Chester"
.AddItem "Cecil"
.AddItem "Cumberland"
.AddItem "Delaware"
.AddItem "Philadelphia"
.AddItem "Lehigh"
.AddItem "Monroe"
.AddItem "Montgomery"
.AddItem "Kent De"
.AddItem "Gloucester"
.AddItem "Lehigh"
.AddItem "Newcastle De"
.AddItem "Salem"
With ComboBox2
.AddItem "Government"
.AddItem "K-12 Education"
.AddItem "Banking/Finance"
.AddItem "Higher Ed."
.AddItem "Healthcare"
.AddItem "Manufacturing"
.AddItem "Hospitality"
.AddItem "Religious"
.AddItem "Retail"
.AddItem "Real Estate"
.AddItem "Entertainment"
.AddItem "Transportation"
.AddItem "Utility"
With ComboBox3
.AddItem "Target"
.AddItem "Managed"
.AddItem "Key"
.AddItem "New"
With ComboBox4
.AddItem "Fire Alarm"
.AddItem "Sprinkler"
.AddItem "Suppression"
.AddItem "Extinguishers"
.AddItem "Intrusion Alarm"
.AddItem "Access Control"
.AddItem "CCTV"
.AddItem "Audio-Video"
.AddItem "Healthcare Communications"
.AddItem "Visitor Management"
.AddItem "Education Electronics"
With ComboBox5
.AddItem "Fire Alarm"
.AddItem "Sprinkler"
.AddItem "Suppression"
.AddItem "Extinguishers"
.AddItem "Intrusion Alarm"
.AddItem "Access Control"
.AddItem "CCTV"
.AddItem "Audio-Video"
.AddItem "Healthcare Communications"
.AddItem "Visitor Management"
.AddItem "Education Electronics"
End With
End With
End With
End With
End With
End Sub
Bookmarks