How to Make Macro Enabled Form in Excel Sheet
In this post, we will discuss about an automated data entry form developed in Excel and VBA. This is simple form to demonstrate how can you transfer the data from a form to a worksheet. Using the same type of code and techniques, you can develop a complex form to transfer and collate the data in a Excel Sheet.
Below is the snapshot of form.
Data Entry Form
Let’s design this form from scratch.
- Open the Excel Application
- Create a new workbook
- Save the workbook as Data Entry Form.xlsm (macro enabled)
- Rename the Sheet1 to Form
- Add another sheet and rename it to Data
- Add Column’s name (Serial No., Name, Sex, Qualification, City, State, Country) in range A1:G1 in Data sheet
- We will utilize this table to store the data which will be transferred through Data Entry Form
- Let’s create the form with ActiveX Controls available in Developer Tab under Control group
- Add Text Box for Name, Option Buttons for Sex, Combo Box for Qualification, 3 additional Text Boxes for City, State & Country and two Command Buttons for Save and Reset.
Post adding the ActiveX Controls and giving the required labels for controls and creating heading (refer the control name mentioned in above snapshot), press F11 to open the VBE Window
- Now double click on Sheet1 to open the code view of Sheet1
- Paste the below code to validate the inputs entered by user in Form
Function ValidateForm() As Boolean
txtName.BackColor = vbWhite
cmbQualification.BackColor = vbWhite
txtCity.BackColor = vbWhite
txtState.BackColor = vbWhite
txtCountry.BackColor = vbWhite
ValidateForm = True
If Trim(txtName.Value) = "" Then
MsgBox "Name can't be left blank.", vbOKOnly + vbInformation, "Name"
txtName.BackColor = vbRed
txtName.Activate
ValidateForm = False
ElseIf optMale.Value = False And optFemale.Value = False Then
MsgBox "Please select sex.", vbOKOnly + vbInformation, "Sex"
ValidateForm = False
ElseIf cmbQualification.Text <> "10th" And cmbQualification.Text <> "10+2" And _
cmbQualification.Text <> "Bachelor Degree" And cmbQualification.Text <> "Master Degree" And _
cmbQualification.Text <> "PHD" Then
MsgBox "Please select the correct qualification from drop down.", vbOKOnly + vbInformation, "Qualification"
cmbQualification.BackColor = vbRed
cmbQualification.Activate
ValidateForm = False
ElseIf Trim(txtCity.Value) = "" Then
MsgBox "City name can't be left blank.", vbOKOnly + vbInformation, "City name"
txtCity.BackColor = vbRed
txtCity.Activate
ValidateForm = False
ElseIf Trim(txtState.Value) = "" Then
MsgBox "State can't be left blank.", vbOKOnly + vbInformation, "State Name"
txtState.BackColor = vbRed
txtState.Activate
ValidateForm = False
ElseIf Trim(txtCountry.Value) = "" Then
MsgBox "Country can't be left blank.", vbOKOnly + vbInformation, "Country Name"
txtCountry.BackColor = vbRed
txtCountry.Activate
ValidateForm = False
End If
End Function
- Add the below mentioned VBA code to reset the form
Function Reset()
Application.ScreenUpdating = False
txtName.Value = ""
txtName.BackColor = vbWhite
optMale.Value = False
optFemale.Value = False
cmbQualification.Text = ""
cmbQualification.BackColor = vbWhite
txtCity.Value = ""
txtCity.BackColor = vbWhite
txtState.Value = ""
txtState.BackColor = vbWhite
txtCountry.Value = ""
txtCountry.BackColor = vbWhite
Application.ScreenUpdating = True
End Function
- Add the below code to call the Reset Function on click event on cmdReset
Private Sub cmdReset_Click()
Dim i As Integer
i = MsgBox("Do you want to reset this form?", vbQuestion + vbYesNo + vbDefaultButton2, "Form Reset")
If i = vbYes Then
Call Reset
End If
End Sub
- Add the below code to transfer input data from ‘Form’ sheet to ‘Data’ sheet on click event for cmdSave
Private Sub cmdSave_Click()
Application.ScreenUpdating = False
Dim iRow As Long
iRow = Sheets("Data").Range("A1048576").End(xlUp).Row + 1
If ValidateForm = True Then
With ThisWorkbook.Sheets("Data")
.Range("A" & iRow).Value = iRow - 1
.Range("B" & iRow).Value = txtName.Value
.Range("C" & iRow).Value = IIf(optMale.Value = True, "Male", "Female")
.Range("D" & iRow).Value = cmbQualification.Text
.Range("E" & iRow).Value = txtCity.Value
.Range("F" & iRow).Value = txtState.Value
.Range("G" & iRow).Value = txtCountry.Value
End With
Call Reset
Else
Application.ScreenUpdating = True
Exit Sub
End If
Application.ScreenUpdating = True
End Sub
- Press CTRL + S to save the code and close the VBA window
- In Excel, come out from designing mode and start entering data in ‘Form’ sheet and Click on Save to Transfer the data
- Click on ‘Reset’ button to reset the form
Please watch our YouTube tutorial on ‘How to make macro enabled Form in Excel sheet’
Please click on the below button to download the Automated Data Entry Form with VBA code.
Hi, i was looking the “Data Entry Form” macro and hove one question. when i click save at the end. will the macro save the input data in the “data sheet” automatically or i have to press save before i close the excel? if not can you send me a code to add so that i don’t have to click save.
Thanks for your help
good learning