Creating a userform in Excel - VBA

Before proceeding you should go through our last project we worked in as we will be modifying that project :-Check this out!!

Scenario :-

• You have to create a form to enter the data instead of manually entering it.
• Validation of form is to be done as no invalid data enters into our database.
• You need to check whether name is not null and marks are not less more than 100.
• You need to add two button in our form one to submit the data and form should not close(earlier values should be clear and form should be ready to take new values) and other to close the form.
• When you are submitting the form, data should enter into our database.
• When you are clicking on close ,it should call the calculation script to calculate the rest of the data and then close the form.

Let's Begin :-

In excel -

• Go to the developer option and click on visual basic.
• In visual basic tab click on Insert -> Userform

Now we will create the basic User Interface of userform.

Following Screen should appear after clicking on Userform.

Now we will add the following objects to our form -

• 6 Label(Studentname , marks1 , marks2 , marks3 , marks4 , marks5)
• 6 Textbox to take inputs(for Studentname , marks1 , marks2 , marks3 , marks4 , marks5)
• 2 buttons(Submit and Cancel)

Use the Toolbox to add all this objects.

Now, we will give proper names to our objects as we have to point those objects through their names. For Labels, select the object and change the caption while for rest of the objects change the names.

Label1	Label2	Label3	Label4	Label5	Label6
Student Name	marks_1	marks_2	marks_3	marks_4	marks_5

Textbox1	Textbox2	Textbox3	Textbox4	Textbox5	Textbox6
txtname	txtm_1	txtm_2	txtm_3	txtm_4	txtm_5

Button1	Button2
btnsubmit	btncancel

Make sure you change the names before proceeding. Follow the step to open the coding tab as shown below

First we will initialize our textbox value's to null.So whenever we open our form it doesn't take dummy values.

'UserForm is the name of our form we created take care while defining the name of the form as we have defined names for our textboxes.

Private Sub UserForm_Initialize()
   txtname.Value = ""
   txtm_1.Value = ""
   txtm_2.Value = ""
   txtm_3.Value = ""
   txtm_4.Value = ""
   txtm_5.Value = ""
End Sub

Now we will write the code for our submit button.

• Define the empty row
• Validating the entry
• Transferring information to our database
• Data we entered into the database is in text format changing it to number format
• Changing the value of our textbox to null to accept new values

Private Sub btnsubmit_Click()
    Dim emptyRow As Long

'Make Sheet1 active

    Sheet1.Activate

'Determine emptyRow

    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Determine Validations

    If txtname.Value = "" Then
    MsgBox ("worng entry at name")
    txtname.Value = ""
    Exit Sub
    ElseIf txtm_1.Value > 100 Or txtm_1.Value = "" Then
    MsgBox ("worng entry at m1")
    txtm_1.Value = 0
    Exit Sub
    ElseIf txtm_2.Value > 100 Or txtm_2.Value = "" Then
    MsgBox ("worng entry at m2")
    txtm_2.Value = 0
    Exit Sub
    ElseIf txtm_3.Value > 100 Or txtm_3.Value = "" Then
    MsgBox ("worng entry at m3")
    txtm_3.Value = 0
    Exit Sub
    ElseIf txtm_4.Value > 100 Or txtm_4.Value = "" Then
    MsgBox ("worng entry at m4")
    txtm_4.Value = 0
    Exit Sub
    ElseIf txtm_5.Value > 100 Or txtm_5.Value = "" Then
    MsgBox ("worng entry at m5")
    txtm_5.Value = 0
    Exit Sub
    End If

'Transfer Information

   Cells(emptyRow, 1).Value = txtname
   Cells(emptyRow, 2).Value = txtm_1
   Cells(emptyRow, 3).Value = txtm_2
   Cells(emptyRow, 4).Value = txtm_3
   Cells(emptyRow, 5).Value = txtm_4
   Cells(emptyRow, 6).Value = txtm_5
   Range("B" & emptyRow & ":F" & emptyRow).Select
   With Selection
        .NumberFormat = "General"
        .Value = .Value
   End With
   txtname.Value = ""
   txtm_1.Value = ""
   txtm_2.Value = ""
   txtm_3.Value = ""
   txtm_4.Value = ""
   txtm_5.Value = ""
End Sub

Now we are able to validate the data before entering into our database.

Now let's build our logic for cancel button

• It will collapse the form.
• Later it will call the calculation macros.

Private Sub btncancel_Click()
   Unload Me
   Call Project_Cal_Grade
End Sub

Add a button to our excel sheet which will call the Userform.

Click on the button to open the script.Paste the following code and we are done.

Sub Button1_Click()
UserForm.Show
End Sub

So our final code will be :-

Private Sub UserForm_Initialize()
   txtname.Value = ""
   txtm_1.Value = ""
   txtm_2.Value = ""
   txtm_3.Value = ""
   txtm_4.Value = ""
   txtm_5.Value = ""
End Sub

Private Sub btnsubmit_Click()
    Dim emptyRow As Long
   'Make Sheet1 active
    Sheet1.Activate
   'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
    'Determine Validations
    If txtname.Value = "" Then
    MsgBox ("worng entry at name")
    txtname.Value = ""
    Exit Sub
    ElseIf txtm_1.Value > 100 Or txtm_1.Value = "" Then
    MsgBox ("worng entry at m1")
    txtm_1.Value = 0
    Exit Sub
    ElseIf txtm_2.Value > 100 Or txtm_2.Value = "" Then
    MsgBox ("worng entry at m2")
    txtm_2.Value = 0
    Exit Sub
    ElseIf txtm_3.Value > 100 Or txtm_3.Value = "" Then
    MsgBox ("worng entry at m3")
    txtm_3.Value = 0
    Exit Sub
    ElseIf txtm_4.Value > 100 Or txtm_4.Value = "" Then
    MsgBox ("worng entry at m4")
    txtm_4.Value = 0
    Exit Sub
    ElseIf txtm_5.Value > 100 Or txtm_5.Value = "" Then
    MsgBox ("worng entry at m5")
    txtm_5.Value = 0
    Exit Sub
    End If
   'Transfer Information
   Cells(emptyRow, 1).Value = txtname
   Cells(emptyRow, 2).Value = txtm_1
   Cells(emptyRow, 3).Value = txtm_2
   Cells(emptyRow, 4).Value = txtm_3
   Cells(emptyRow, 5).Value = txtm_4
   Cells(emptyRow, 6).Value = txtm_5
   Range("B" & emptyRow & ":F" & emptyRow).Select
   With Selection
        .NumberFormat = "General"
        .Value = .Value
   End With
   txtname.Value = ""
   txtm_1.Value = ""
   txtm_2.Value = ""
   txtm_3.Value = ""
   txtm_4.Value = ""
   txtm_5.Value = ""
End Sub

Private Sub btncancel_Click()
   Unload Me
   Call Project_Cal_Grade
End Sub

SHARE

Shubu

Hi. I’I hope this post was helpful for you.