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.