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

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

    • 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
    • Add the below mentioned VBA code to reset the form
    • Add the below code to call the Reset Function on click event on cmdReset

    • Add the below code to transfer input data from ‘Form’ sheet to ‘Data’ sheet on click event for cmdSave
    • 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.

    Click to download

    1 COMMENT

    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