How to get data from a user-form into a table in an Excel worksheet with VBA. Last time we learnt how to create an inventory solution using tables in a single worksheet. We also learnt how to create an inventory management solution using worksheets like Items list, Items received, Items issued and Stock calculations. We used formulas like Vlookup, COUNTIF and SUMIF. Today we describe how to find the next blank row in a table for data entry. This will be a precursor to creating an inventory solution with tables in combination with user-forms. Watch the video to learn more:
Watch the video on YouTube.
Here’s the complete VBA code:
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = ActiveSheet.ListObjects(“Table1″).Range
Dim LastRow As Long
LastRow = rng.Find(What:=”*”, _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
rng.Parent.Cells(LastRow + 1, 1).Value = TextBox1.Value
rng.Parent.Cells(LastRow + 1, 2).Value = TextBox2.Value
rng.Parent.Cells(LastRow + 1, 3).Value = ComboBox1.Value
End Sub
Private Sub CommandButton2_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Or TypeName(ctl) = “ComboBox” Then
ctl.Value = “”
End If
Next ctl
End Sub
Private Sub UserForm_Initialize()
frmItemsList.ComboBox1.List = Array(“can”, “bag”, “doz”, “kg”)
End Sub
Further reading:
