How to Make Simple User-form in Microsoft Excel Using Visual Basic

Entering data’s on your WorkBook sometimes tiring, why not create a simple form for you to conveniently add information on your report sheet, grade sheet etc. In this simple tutorial we are going to learn how to create simple form using simple coding with VBA in Excel.

Your designed Excel user-form will not just offer advancement with the key ideas of Microsoft Excel but also provides elegance and knowledge advancement with your colleagues.

Here are the simple steps to follow to complete your Excel User-form.

Step 01: Open your Microsoft Excel

Step 02: Click any and then “Press Alt+F11”

Step 03: Click “Insert Tab” and Choose “Userform”

Step 04:

1             1. Click Label button

               2. Draw out where you want your labels (text)

Step 05:  Create 4 labels in total with the same procedure as Stated on Step 04 and make 

               sure to change the “Name” of every label.

Step 06: you can change the font style under these options

Step 07: Now let us create a text box, to create simple follow the picture below by 

              clicking the “textbox” make sure to name the text box as “textbox_name” the                  same thing will happen to surname, age and gender.

Step 08: Now let us create a Command button

              1. Click Command Button

              2. Draw the Button

3            3. Change the Caption “Add Details”

              4. Change the Name “Cmdbutton_add”

              5. The same thing will happen with the Close Form but the name should be 

                  “Cmdbutton_close”

Step 09: Now let us Name our project as Computer Lesson “ to change the text at the top 

               of the window rename the ‘Caption” Section.

Step 10: To apply Commands

1           1.  Right Click the “add details” button

2                  2. Click View Code

             3. Insert the Code Just copy below and then close

             4. For the “Close Form” the same thing right click and then type “Unload Me” then 

                  close

The Code:

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a Name number
If Trim(Me.textbox_name.Value) = "" Then
Me.textbox_Name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.textbox_name.Value
ws.Cells(iRow, 2).Value = Me.textbox_surname.Value
ws.Cells(iRow, 3).Value = Me.textbox_age.Value
ws.Cells(iRow, 4).Value = Me.textbox_gender.Value

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.textbox_name.Value = ""
Me.textbox_surname.Value = ""
Me.textbox_age.Value = ""
Me.textbox_gender.Value = ""
Me.textbox_name.SetFocus

Step 11: Now go back to Excel and follow tweak to show your Visual Basic Commands

            1. Click “View”
       2. Choose “Option 2”
       3. Click “Customize”
       4. Choose “Developer Tab”
       5. Click “Insert Controls”
       6. Click “add”

7          7. It should appear like that and hit ok

Step 12: Create Simple Data Heading and Insert Controls

            1. Click “Form Controls”

            2. Choose “Insert Command”

            3. Draw your button

Step 13:

            1. After drawing your button

            2. Type “userform”

            3. Hit “New’

            4. Type “UserForm1.Show”

            5. Close

Step 14: Running your Program

            1.  Click “Add Details”

            2. Enter the Needed Information

            3. Click “Add Details”

            4. Data Added 

Congratulations!!!

You have just created a simple data entry form in Microsoft excel using Visual Basic. Hope you learn something and if you have questions Just let me know and it will be my honor answering your questions.