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

Get FREE Advanced Excel Exercises with Solutions!

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.

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 in Excel VBA to Create the Data Entry Form

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 sub procedure 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 sub procedure called CommandButton_Click will open. Enter the following code there.

⧭ VBA Code:

Private Sub CommandButton1_Click()
Total_Rows = ActiveSheet.UsedRange.Rows.Count + 1
Active_Column = 1
Set Top_Cell = ActiveSheet.Range("B2")
 For i = 1 To Total_Rows
    If Top_Cell.Cells(i, 1).Value = "" Then
        For Each Ctrl In UserForm1.Controls
            If TypeName(Ctrl) = "TextBox" Then
                Top_Cell.Cells(i, Active_Column).Value = Ctrl.Text
                Active_Column = Active_Column + 1
            End If
        Next Ctrl
    Exit For
    End If
Next i
End Sub

VBA Code for Inserting Data Using Userform

📌Note: Here, we have hard-coded the Top_Cell as cell B2. So make sure that the headings start at B2 in all the worksheets.
  • 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.


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.

  • Click and open the Developer tab from the Excel Ribbon on the top. The Developer tab is by default hidden in Excel. If you don’t see it in your Excel Ribbon, follow the steps mentioned here to display the Developer tab on the ribbon.

  • 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.


Step 3: Viewing the Final Output of 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.

Download Practice Workbook

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


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. Thank you!


Related Articles


<< Go Back to Data Entry Form in Excel | Data Entry in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

6 Comments
  1. Reply
    Yiannis Zouganelis Dec 12, 2022 at 4:45 AM

    Hi !
    Thanx for the very helpfull article !

    Is it possible to have the ENTER NEW DATA button to a different worksheet and when we click on the ENTER NEW DATA button and select the worksheet on which we want to make the new entry (New York, Washington,California) the worksheet NOT to become active?

    How vba can be modified in order not only to enter new data to one of these three worksheets, but also to search for a name or address in the already added entries?

    • Hey, YIANNIS ZOUGANELIS!
      Thank you for your query. Hope you are doing well. You have asked some thoughtful questions. I am answering all your queries one by one below.

      Q1: First of all, you have asked if it is possible to have the ENTER NEW DATA button on a different worksheet.
      Yes, this is very much possible. In this regard, you will have to follow the same procedures of the article to create the forms and buttons for everything in the worksheet just where you want the button to appear.

      Q2: Second, you want the selected worksheet not to become active. In this regard, you have to change the code a little bit. Say, you have set the button in the MainSheet worksheet. Now, you want to be active in this sheet all along. You don’t want to activate any other selected worksheet.
      In this regard, create the button and form in the MainSheet worksheet and then write the code below inside the Code window of the Command_Button1.
      Code:
      Private Sub CommandButton1_Click()
      TargetSheet = ListBox1.Value
      If TargetSheet = "" Then
      Exit Sub
      End If
      Worksheets(TargetSheet).Activate
      lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
      ActiveSheet.Cells(lastRow + 1, 2).Value = TextBox1.Value
      ActiveSheet.Cells(lastRow + 1, 3).Value = TextBox2.Value
      ActiveSheet.Cells(lastRow + 1, 4).Value = TextBox3.Value
      ActiveSheet.Cells(lastRow + 1, 5).Value = TextBox4.Value
      Worksheets("MainSheet").Activate
      End Sub

      Q3: Thirdly, you want to search for present values rather than entering values. This is a different thing. Say, you are given the same dataset as per the article. Now, you want to enter only the customer’s name and want to get the contact address, age, and gender. Go through the steps below to achieve this.

      Steps:

      • First, you will need to create a new user form for this.
      • To create a new user form, go to the Developer tab >> Insert tool >> Button (Form Control option) from the Form Controls group.
        Insert Form Control Button
      • As a result, a button would appear.
      • Now, name the button as you like (I have named SEARCH for DATA)and right-click on the button.
      • Following, choose the Assign Macro… option from the context menu.
        Assign Macro to the Button
      • Thus, the Assign Macro window would appear.
      • Here, choose a Macro name as you wish and click on the New button.
        Macro Window
      • Afterward, a new module would appear in the VB Editor.
      • Following, write the following code in the code window.
        Sub Search_Data() UserForm1.Show End Sub
        Code for Form Button
      • Now, you need to create UserForm1.
      • To do this, go to the Insert tab inside the VB Editor and choose the UserForm option.
        Insert UserForm
      • Consequently, the Toolbox window would appear.
      • Now, choose the option Label from the window and drag it inside the form area to create a label.
        Create a Label
      • After dragging the label, name it.
      • Following, choose the TextBox option from the Toolbox window and drag it inside the form area.
        Add a Text Box
      • Following, name the text box and repeat the previous procedures to create another label.
        Add another Label
      • Continue to repeat these procedures to create all labels, textboxes, and the search button inside the form.
        UserForm1
      • Now, right-click on your Search button and choose the option View Code from the context menu.
        View Code of the Search Button
      • Afterward, name the Command Button as SearchButton and write the following code in the VB Editor.
        Code:
        Sub SearchButton_Click()
        Dim sh As Worksheet
        Set sh = ThisWorkbook.Sheets("Search")
        Dim lr As Long
        lr = sh.Range("B" & Rows.Count).End(xlUp).Row
        Dim i As Long
        If Application.WorksheetFunction.CountIf(sh.Range("B:E"), Me.TextBox1.Text) = 0 Then
        MsgBox "No match found!", vbOKOnly + vbInformation
        Call Reset
        Exit Sub
        End If
        For i = 2 To lr
        If sh.Cells(i, "B").Value = Me.TextBox1.Text Then
        TextBox1 = sh.Cells(i, "B").Value
        TextBox2 = sh.Cells(i, "C").Value
        TextBox3 = sh.Cells(i, "D").Value
        TextBox4 = sh.Cells(i, "E").Value
        End If
        Next i
        End Sub
        Function Reset()
        TextBox1.Value = ""
        TextBox2.Value = ""
        TextBox3.Value = ""
        TextBox4.Value = ""
        End Function
      • Afterward, save the Excel file as .xlsm file to enable the macro.
      • Now, click on your first created button SEARCH for DATA. Thus, the user form will appear.
        SEARCH for DATA Button
      • Now, say, you want to find data for Craig Arvin.
      • Insert the name in the first textbox. And, click on the Search button.
        Search an Entry

      Finally, you will be able to get your desired automated search result.
      Search Result

      Regards,
      Tanjim Reza

  2. Hi, I had a question, I noticed that when I deleted an entry on row 1 for example wrong data was entered, then I add the data again via the form, it should insert the new data into row 1, however, it will leave a blank and insert the data into the row 2. Is there something I had miss out?

    • Thank you GLENN for bringing the issue you faced to our attention. We are sorry to hear that you faced the problem regarding the position of new entries. We have updated the code and Excel file. In the revised code, we have hard-coded the Top_Cell as cell B2 in CommandButton1_Click() subroutine. So make sure that, the headings start at B2 in all the worksheets. Now, you can download the new file and try it again. Hopefully, you will not face the problem anymore.
      Sincerely,
      Aniruddah
      Exceldemy

  3. Hello, I have a question. How can I alter the code so that you don’t have to choose a worksheet but instead it enters new data on the worksheet you are currently on?

    Thanks in advance

    • Hi JASON,
      Thank you for your comment. According to your comment, I understand that you want to alter the code so that you don’t have to choose a worksheet but instead, it enters new data on the worksheet you are currently on.

      For this, you don’t need any ListBox named ListBox1. Follow the below steps:
      ● In Step 1, while developing the UserForm to create the Data Entry Form, you don’t need to put ListBox1 as there will be no selection option according to your query.

      So the UserForm will look like the following image.
      image 1

      ● Now, in Module 1 insert the following code and save it.

      Sub Run_UserForm()
      UserForm1.Caption = “Data Entry Form”
      Load UserForm1
      UserForm1.Show
      End Sub

      image 2
      Now, you are good to go. You will not have to choose a worksheet instead, it enters new data on the worksheet you are currently on.

      Here is a sample image. I have entered data in the worksheet named Washington.
      image 3

      Hopefully, you will be able to solve your problem now. Please feel free to reach out to us with any other questions or you can send us your Excel files as well.

      Regards
      Nujat Tasnim
      Exceldemy.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo