How To Create UserForm In Excel VBA

When you want to input data into Excel cells, you can input it directly. But when there are so many rows in your worksheet, you may need to scroll a lot of rows to input the new row data. With Excel user forms, you can input data with forms like the usually used web forms on web pages. And it makes input data into Excel rows easy. This article will tell you how to create a user form in Excel using VBA and how to use it to input row data in an Excel worksheet.

1. How To Create A User Form In Excel Using VBA.

1. Click the Developer tab in the Excel ribbon, then click the Visual Basic item in the Code group.
2. Then it will open the Microsoft Visual Basic for Applications window.
3. If you can not find the Developer tab, please read the article How To Enable Developer Tab And Write VBA Source Code In Excel to learn more.
4. Click the Insert —> UserForm menu item in the Microsoft Visual Basic for Applications window top menu bar to create a user form.
5. Then you can see there is a new user form created in the VBA window left side, the form name is UserForm1, and it is located in the VBAProject / Forms folder.
6. Click UserForm1, it will show the user form designer window on the right side.
7. Right-click UserForm1, and then you can toggle the form GUI designer and source code editor by clicking the View Code / View Object menu item in the popup menu list.
8. We click the View Object sub-menu to show the UserForm designer window on the right side.

2. How To Add Controls On The User Form.

1. Open the user form designer, when you click the form, it will show the ToolBox window beside the form.
2. There are some controls on the form window.
3. In this example, we add 2 label text controls ( User Name, and Password), 1 input text box for username, 1 password input text box for the password, and 2 buttons ( Regist, Reset ).
   
4. You can just click the related components in the ToolBox, then drag it to the form body.
5. After you add a related control in the user form, you can set the control’s Name, Caption, and Text properties in the control’s properties list under the VBAProject panel.

3. How To Add Source Code When User Click The Buttons On User Form.

1. Double click the Regist button, then it will open the source code editor window, and add the below function in it.

   Private Sub BtnRegist_Click()
      
   End Sub

2. The above function will be invoked when the user clicks the Regist button.
3. The complete source code of the above function is as below, when the user clicks the Regist button, it will input the user name and password into the Excel worksheet with the tab name Sheet1. You can see the code comments for a detailed explanation.

   Private Sub BtnRegist_Click()
      
      ' Get the row number of the first not empty row from excel the last row to top.
      notEmptyRowNumber = Sheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row
   
      ' Assign the user name value in the user form to the A column in the first empty row from the top.
      Range("a" & notEmptyRowNumber + 1) = IdUserName.Value
     
      ' Assign the password value in the user form to the B column in the first empty row from the top.
      Range("b" & notEmptyRowNumber + 1) = IdPassword.Value
   
      ' Empty username & password input text value in the user form.
      IdUserName.Value = ""
      IdPassword.Value = ""
      
   End Sub

4. Add the below function to process the click event for the button Reset.

   Private Sub BtnReset_Click()
   
      ' Reset username & password input text value in the user form.
      IdUserName.Value = ""
      IdPassword.Value = ""
      
   End Sub

5. Now you can run the user form and input the user name and password, when you click the Regist button, you can see the added user name and password in the Excel sheet1.

4. How To Show The User Form In Excel.

1. After you create and write the source code for the user form in Excel, you can use it in your Excel worksheet.
2. But before you can use it, you need to display it on the screen. We will use a VBA macro to display the UserForm on the screen as below.
3. First, click the Insert —> Module menu item in the Microsoft Visual Basic for Applications window.
4. Then it will add a module file Module1 in the VBAProject / Modules folder on the window left side.
5. Write the below source code in the module file, the below source file will create a macro with the name Regist_User_Account. It will show the user form when the macro is invoked.

   Sub Regist_User_Account()
   
     UserForm1.Show
   
   End Sub
   

6. When the above macro is invoked, it will show the UserForm1.

5. How To Use The User Form In Excel WorkSheet.

1. Now, it is time to use the UserForm in your Excel worksheet.
2. Open the Excel worksheet, and click the Developer tab.
3. Click the Insert —> Button ( Form Control ) in the Controls group.
4. Then it will pop up a dialog window with the title Assign Macro.
5. Select the above macro that is created in section 4 from the Macro name list.
6. Click the OK button, then it will close the Assign Macro window and add a button on the sheet.
7. You can change the button size, and change the button text to Regist User Account.
8. Now when you click this button, it will call the macro and display the user form, then you can input user account data in the user form.