Creating VBA Form in Excel for Data Entry

Today we will learn an advance and very useful topic in VBA Excel. We will learn to create a VBA form for data entry in MS Excel.
Although MS Excel has already great interface to work with rows and columns but there are always chances where existing data may be edited or deleted while working. To prevent such mistakes, we can make an abstract view of Excel sheet and offer a simple VBA form to fill data into Excel sheet. This is very useful when you are assigning data entry job to a new excel worker. Lets start this step by step tutorial of creating form and using the same in Excel 2016, 2013 / 2010 / 2007.

First thing in order to make a fully data entry working form, we will create a simple VBA form. Open a new Excel file and press ALT + F11 shortcut key to open Microsoft Visual Basic Editor. Then press CTRL + R to open the list of Modules and Forms in VBA project window.

To create a VBA form, click on “Insert User Form” icon and select “UserForm” option.

A new form will open with Toolbox Window. If Toolbox Window does not open, you may get it from View Menu option. Toolbox has various VBA components like TextBox, Combobox, RadioButton, ListBox etc. Here we will insert one TextBox, one ComboBox and Labels before them. Finally a Command Button at the end to execute form.

Now double click on command button, which will open CommandButton1_Click(). Use below code for CommandButton1_Click():

Private Sub CommandButton1_Click()
Dim i As Integer
i = 1
While ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value <> ""
i = i + 1
Wend
ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value = TextBox1.Value
ThisWorkbook.Worksheets("Sheet1").Range("B" & i).Value = ComboBox1.Value
End Sub

Lets add few options in ComboBox. Paste below lines of code to insert options in ComboBox:

Sub UserForm_Initialize()
ComboBox1.List = Array("1001", "1002", "1003", "1004", "1005")
End Sub

It will add items to ComboBox while initializing the form. Do not change ”UserForm” in Sub UserForm_Initialize().

Once form is ready, you may required a simple macro which will initiate the form at the opening of Excel file. Go to Insert Menu option and click on Module in VBA Editor window then paste below code in it.

Sub Auto_Open()
UserForm1.Show
End Sub

This will open the VBA form while opening Excel workbook. Change the UserForm1, if you have renamed the form.

Now minimize the VBA editor window and come back to excel sheet. Save it as Macro Enable Workbook and close.

Reopen the file and enable macros. The VBA form will appear in the excel sheet on enabling Macro. Now you can fill the data in TextBox and choose the value from ComboBox. On clicking of submit button you can see data being inserted in excel sheet. You can add more TextBoxes and other components in form to insert more fields in Excel.

Validating Data in VBA Form:

Now the advance part of data validation starts. It is possible that the person inputting leaves a field blank or fill text data into a numeric field. To prevent such instances, you may use data validation. Data validation may be depending upon individual’s requirement. Here we will give a short example of validating values. For instance there is possibility that the user submit form without filling value in TextBox or does not select a value from ComboBox. In this case we can set validation by adding following code of lines in Form:

Private Sub datavalidation()
If Not IsNumeric(TextBox1.Value) Then
MsgBox "Sorry, Name Field can not be blank."
Exit Sub
End If
If ComboBox1.Value = "" Then
MsgBox "Sorry, You need to select at least one Emp No."
Exit Sub
End If
End Sub

Also add a following line just before End Sub in CommandButton1_Click().

datavalidation

You can Validate fields on different parameters like checking if number, checking if email address, checking if standard PIN code etc.

Hope you can now create a basic inputting form in Excel.