Data Entry Form

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

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)
    Save As
  • 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
  • Data
  • 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
ActiveXControls
  •  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.
Form
Creating Form
  • 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

    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.

    Download e1574918596847
    Click to download

    2 COMMENTS

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

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here

    Captcha verification failed!
    CAPTCHA user score failed. Please contact us!

    Recommended Reads

    Latest Articles