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.
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:
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." 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
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 = "" txtname.SetFocus 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() studentenquiry.Show End Sub