Create Userform in Excel VBA

Student Enquiry User form in Excel VBA


In this example we will create a User form to ask students to fill information regarding courses offered by your Institution or college. User can fill the information like Name, Course Type, Contact No., Description and Address and data will populate in the excel worksheet.

Note: Forms are objects in Excel that help us to enter data quickly and easily.

Click Here to download Excel workbook

Prepare Excel Worksheet (which will work as Database)

This step is to create a new excel worksheet which will record six information about the student: Student ID, Name, Course Type, Description, Contact No. and Address.


Designing User Form

In this step we will create userform in excel vba and add will controls to the form:

    • Open VBE (Visual Basic Editor), Alt + F11. Under Developer Tab, under Code Panel, click Visual Basic


    • Click Insert |User Form( If toolbox menu is not appearing click View | Tool Box).


  • To insert controls, select it from the Toolbox and drag it to where you want it in the Form.

Use the below mention settings for each controls of your user form:


Student Enquiry Form in Excel VBA

Copy and Paste the below mentioned code in the Forms Code sheet ( To View form code sheet, double click on the form).

Submit Button Code

Private Sub cmdsubmit_Click()

Dim lastrow As Long

' below code will make user mandatory to fill name and contact no. and conatact no. as numeric value
If Me.txtname.Value = "" Then
        MsgBox "Please enter a Name."
        Exit Sub
End If
If Me.txtnumber.Value = "" Then
        MsgBox "Please enter a contact no."
        Exit Sub
End If
If Not IsNumeric(txtnumber.Value) Then
       MsgBox "Contact No. should be numeric"
       Exit Sub
End If

'Input data in your excel worksheet
lastrow = Worksheets("Enquiry").Range("A65536").End(xlUp).Row

With Worksheets("Enquiry").Range("a1")
  .Offset(lastrow, 0).Value = lastrow
  .Offset(lastrow, 1).Value = Me.txtname.Value
  .Offset(lastrow, 2).Value = Me.cbocoursetype.Value
  .Offset(lastrow, 3).Value = Me.txtdescription.Value
  .Offset(lastrow, 4).Value = Me.txtnumber.Value
  .Offset(lastrow, 5).Value = Me.txtaddress.Value
End With

'clear the form
cbocoursetype.Value = ""
txtname.Value = ""
txtdescription.Value = ""
txtnumber.Value = ""
txtaddress.Value = ""

End Sub

Cancel Button Code

Private Sub cmdcancel_Click()
   Unload Me
End Sub

Clear Button Code

Private Sub cmdclear_Click()
   Call UserForm_Initialize
End Sub

Userform Initialize Code

Private Sub UserForm_Initialize()

 txtname.Value = ""
 txtdescription.Value = ""
 txtnumber.Value = ""
 txtaddress.Value = ""

With cbocoursetype
  .AddItem "Software Testing"
  .AddItem "Hardware"
  .AddItem "Designing"
  .AddItem "Programming "
  .AddItem "Andriod Devlopement"
End With

cbocoursetype.Value = ""

End Sub

A macro to open the User form

Create Command button on the “Enquiry” worksheet and write below code behind it.

Private Sub CommandButton1_Click()
End Sub

Create Userform in Excel VBA

October 22, 2012

4 Responses on Create Userform in Excel VBA"

  1. Tsegaye Miniwagw says:

    I appriciate your big talent in excel you belong to for us IT Trainers and i have asking you how can i make screen resolution for any PC in excel VBA.

  2. Mounir says:

    Useful information

Leave a Message

Your email address will not be published. Required fields are marked *

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com

Your Name (required)

Your Email (required)

Your Phone no. (required)

Your Location (required)

Your Message for us...