+91-8826547882

Create Userform in Excel VBA

Student Enquiry User form in Excel VBA

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

Userform1_excelcrazy_training_in_delhi

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

VBA

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

VBA-Userform

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

Controls

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

[code lang=”VB”]
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."
Me.txtname.SetFocus
Exit Sub
End If
If Me.txtnumber.Value = "" Then
MsgBox "Please enter a contact no."
Me.txtnumber.SetFocus
Exit Sub
End If
If Not IsNumeric(txtnumber.Value) Then
MsgBox "Contact No. should be numeric"
Me.txtnumber.SetFocus
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
[/code]

Cancel Button Code

[code lang=”vb”]
Private Sub cmdcancel_Click()
Unload Me
End Sub
[/code]

Clear Button Code

[code lang=”VB”]
Private Sub cmdclear_Click()
Call UserForm_Initialize
End Sub
[/code]

Userform Initialize Code

[code lang=”VB”]
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 = ""
txtname.SetFocus

End Sub
[/code]

A macro to open the User form

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

[code lang=”VB”]
Private Sub CommandButton1_Click()
studentenquiry.Show
End Sub
[/code]

Create Userform in Excel VBA

October 22, 2012

4 Responses on Create Userform in Excel VBA"

  1. Mounir says:

    Useful information

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

Leave a Message

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