How to Create Data Entry Form in Excel VBA (with Easy Steps)

While working with VBA in Excel, most of us face problems while trying to create a data entry form. In this article, I’ll show you how you can create a data entry form in Excel pretty conveniently and handsomely using VBA.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Simple Steps to Create a Data Entry Form in Excel VBA

Here I’ve got three worksheets called Washington, New York, and California that contain the names, contact numbers, ages, and genders of some customers of these three branches of a bank.

Database to Create a Data Entry Form in Excel VBA

Our objective today is to create a form to enter any data into this database using VBA.

We’ll accomplish our objective in two steps.

  • First, we’ll create a data entry UserForm to enter new data;
  • Then we’ll add a button on the worksheets to open the form.

Step 1: Developing a UserForm to Create the Data Entry Form Using Excel VBA

This is a long process and may take some time for you to understand completely. Therefore, for an effective outcome, follow the steps mentioned here with utmost care and patience.

  • Press ALT + F11 on your keyboard to open the Visual Basic window.

Opening the VBA Window to Create a Data Entry Form in Excel VBA

  • In the Visual Basic window, go to the Insert > UserForm option in the toolbar. Click on UserForm. A new UserForm called UserForm1 will open.

  • First, drag a Label (Label1) from the toolbox to the left-most top corner of the UserForm. Change the display of the Label to Worksheet.

Drag a Listbox (Listbox1) to the right of Label1.

  • Then drag a few more Labels equal to the total number of columns of the data set (4 in this example) over the left side of the UserForm. Next to each Label, drag a TextBox.

Change the displays of the Labels to the column headers of your data set (Customer Name, Contact Address, Age, and Gender here).

  • Finally, drag a CommandButton to the right-most bottom side of the UserForm. Change the display of the button to Enter Data.

Dragging Tools to Create a Data Entry Form in Excel VBA

  • Double-click on the Listbox. A private subprocedure called ListBox1_Click will open. Enter the following code there.

⧭ VBA Code:

Private Sub ListBox1_Click()

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
       Worksheets(UserForm1.ListBox1.List(i)).Activate
    End If
Next i

End Sub

ListBox Code to Create a Data Entry Form in Excel VBA

  • Next, double-click on the CommandButton. Another private subprocedure called CommandButton_Click will open. Enter the following code there.

⧭ VBA Code:

Private Sub CommandButton1_Click()

Total_Rows = ActiveSheet.UsedRange.Rows.Count
Total_Columns = ActiveSheet.UsedRange.Columns.Count

Active_Column = 1

For Each Ctrl In UserForm1.Controls
    If TypeName(Ctrl) = "TextBox" Then
        ActiveSheet.UsedRange.Cells(Total_Rows + 1, Active_Column) = Ctrl.Text
        Active_Column = Active_Column + 1
    End If
Next Ctrl

End Sub

  • Finally, go to the Insert > Module option in the toolbar, and click on Module.

Inserting Module to Create a Data Entry Form in Excel VBA

  • A new Module called Module1 will be inserted. Enter the following code there:

⧭ VBA Code:

Sub Run_UserForm()

UserForm1.Caption = "Data Entry Form"

UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.ListStyle = fmListStyleOption

For i = 1 To Sheets.Count
    UserForm1.ListBox1.AddItem Sheets(i).Name
Next i

Load UserForm1
UserForm1.Show

End Sub

UserForm Code to Create a Data Entry Form in Excel VBA

You are done! You’ve successfully created the data entry form to enter any new data into the database.

Read More: Types of Data Entry in Excel (A Quick Overview)


Step 2: Inserting a Button to Open the Data Entry Form

We’ve successfully created the data entry form using a Userform in Excel VBA. Now we’ll add a button to our worksheets to open the form.

Follow the steps mentioned below to attain this.

  • Under the Developer tab, in the section Controls, click on Insert. You’ll find a handful of tools ready to use. Drag a Button (Form Control) from the topmost left side.

Dragging a Button to Create a Data Entry Form in Excel VBA

  • Drag the button to the desired zone on your worksheet and release it. A dialog box called Assign Macro will open. In the Macro name field, insert Run_UserForm.

Assigning Macro to Create a Data Entry Form in Excel VBA

Then click OK.

  • Now, if you want, you can change the display of the button. I’ve changed it to Enter New Data.

Congratulations! You’ve successfully added the button to open the data entry form.

Now, if you want, you can enter a button in each of our worksheets to add more sophistication to the process.

Read More: How to Automatically Insert Timestamp Data Entries in Excel (5 Methods)


Similar Readings


Step 3: Final Output to Create Data Entry Form

We’ve successfully created a data entry form using Excel VBA and added a button to open it. To view the output, click on the button Enter New Data on the worksheet.

The data entry form will open. First of all, select the worksheet on which you want to make the new entry.

Here I’ve selected New York. The moment you select a worksheet, it’ll become active.

  • Then fill up the rest of the data that you want to enter. Here I’ve put Jennifer Marlo, 444204240, 26, and Female.

Inserting Data to Create the Data Entry Form in Excel VBA

  • Then click Enter Data.

You’ll find the new data entered into the last row of the database.

Read More: How to Create an Excel Data Entry Form without a UserForm


Things to Remember

  • While entering new data, the code follows the sequence of the TextBoxes of the UserForm. Therefore, while adding TextBoxes on the UserForm, maintain the sequence of the columns in your database.

Conclusion

Therefore, this is the process to create a data entry form for your database using Excel VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo