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

How to input data from userform into new table row?

Excel 2013 Power Programming with VBA

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

4 thoughts on “Get Data from UserForm into Table”

  1. Hi I have a Situation and I want to resolve it through VBA. Lets say in column B three names are occuring 1000 times again and again (BMW, Audi, VW). I have data infront of all These names which start from column C till Column Y. I want to remove all the data infront of BMW and Audi but want to Keep the data infront of VW. How can I do that by using VBA. Please Response ASAP.

    Many thanks in advance.

  2. Hi Sir I am a student and like your videos.
    I am unable to fix a problem. Actually I am using a combo box which is having 10 fruits’ name
    and in another worksheet I have given the list of those fruits. Each fruit has their own process to serve.
    Like while selecting banana next column in the list says “Make a shake of it and then serve”, symultaniously
    I am assigning the text to a string variable.
    Now I want that if a fruit is selected through combo box , the process will store in that variable and msg box should show that variable …..Thanks

  3. Sorry Dinesh Sir , I am not good in English. I want to transfer data with user form. i have created a file followed your video and others’ video also.But the problem is arriving with both steps. Problem is When i transfer data, its done but when i use vlookup formula most of time its error with only model table not the size table..i want to share that file which have created but i don’t know your email id or others, where i can share that file with you.. I am suffering this problem so long..please help me out as soon as posible.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.