Get Data from UserForm into Table

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, _

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:

How to input data from userform into new table row?

Excel 2013 Power Programming with VBA