REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

Data Entry Userform in Excel Workbook using VBA :Project Objective

Data Entry Userform in Excel Workbook using VBA is used to enter data using userform and update data in the Excel database. I have created userform with following fields like Id, Name, Gender, Location, Email Address, Contact Number and Remarks. You can see the userform design in the following chapter. Following is the step by step detailed explanation to automate this project using VBA. How we are creating Data Entry Userform in Excel Workbook? Let us see!

Data Entry Userform

Data Entry Userform

How we are going to develop this Data Entry Userform (The KEY steps):

Here is the step by step process how we are going to develop this Data entry userform in Excel Workbook using VBA.
Let me explain the key steps to develop this Data entry userform project. We are going to write multiple procedures with the below approach.

  • Step 1: Variable Declaration: We will be declaring required variables and objects which are using in our procedures.
  • Step 2: TurnOff screen update and Events: We are temporarily avoiding screen flickering and events triggering in the application. You can use this at the beginning of the procedure.
  • Step 3: Create userform: Insert userform from the insert menu. Place required controls from the tool box on the userform.
  • Step 4: Create Procedure for field validation: We need to gather the correct format of data from the user. So, we have to validate the data before updating into our Data Worksheet. We are doing this process while user clicks on the ‘Add’ button. i.e. everytime user enter the data and we validate all the fields (Textboxes and radiobuttons) for the correctness of the data
  • Step 5: Create function to find lastrow: Creating function (fn_LastRow) to find lastrow in the data Worksheet to update data into it from userform.
  • Step 6: Create procedure to clear fields of userform: Create procedure (Clear_DataSheet) to clear data Worksheet columns (A to G) of data.
  • Step 7: Cretae procedure to update data to the Worksheet: Create procedure (cmdAdd_Click) to update or add data to the Data Worksheet.
  • Step 8: Create procedure to unload userform: Create procedure(cmdCancel_Click) to exit from the userform. Click on ‘cancel’ button or top right bottom cornerof the window to unload the userform.
  • Step 9: TurnOn screen update and Events: Let’s reset the screen update and events of the application. You can use this at the end of the procedure.

Design of the Data Entry Userform:

Now, let us see the design of the Data Entry UserForm project of each control properties and their values on the userform:

Control Property Value
UserForm Name frmData
Caption UserData
Label Name lblId
Caption ID
Label Name lblName
Caption Name
Label Name lblGender
Caption Gender
Label Name lblEAddr
Caption Email Address
Label Name lblCNum
Caption Contact Numer
Label Name lblRemarks
Caption Remarks
OptionButton Name obMale
Caption Male
GroupNmae g1
OptionButton Name obFMale
Caption FeMale
GroupNmae g1
TextBox Name txtId
Enabled False
TextBox Name txtName
TextBox Name txtLocation
TextBox Name txtEAddr
TextBox Name txtCNum
TextBox Name txtRemarks
MultiLine True
CommandButton Name cmdClear
Caption Clear
CommandButton Name cmdAdd
Caption Add
CommandButton Name cmdCancel
Caption Cancel

This is what I used to create Data Entry UserForm design. By changing or setting all the above properties and values of control the form will be looking like below.

Code and Explantion:

    Step 1: Declaring variables which are using in the entire project.

    'Variable declaration
    Dim txtId, txtName, GenderValue, txtLocation, txtCNum, txtEAddr, txtRemarks
    Dim iCnt As Integer
    

    Step 2: Disable Screen Updating is used to stop screen flickering and Disable Events is used to avoid interrupted dialog boxes or popups.

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With 
    

    Step 3: Create userform:

    Create userform by adding controls to the userform from the Toobox. In this project we are placing seven Labels, two Radio buttons, six Textboxes and three CommandButtons.You can refer the design section of the Data entry userform to create userform.

    Step 4: Create Procedure for field validation.

    We have to validate the data before updating into our Data Worksheet. So, we need to gather the correct format of data from the user. We are doing this process while user clicks on the ‘Add’ button. i.e. everytime user enter the data and we validate all the fields (Textboxes and radiobuttons) for the correctness of the data.

    'Variable Declaration
    Dim BlnVal As Boolean
    
     ' Check all the data(except remarks field) has entered are not on the userform
    Sub Data_Validation()
         If txtName = "" Then
            MsgBox "Enter Name!", vbInformation, "Name"
            Exit Sub
         ElseIf frmData.obMale = False And frmData.obFMale = False Then
            MsgBox "Select Gender!", vbInformation, "Gender"
            Exit Sub
         ElseIf txtLocation = "" Then
            MsgBox "Enter Location!", vbInformation, "Location"
            Exit Sub
        ElseIf txtEAddr = "" Then
            MsgBox "Enter Address!", vbInformation, "Email Address"
            Exit Sub
        ElseIf txtCNum = "" Then
            MsgBox "Enter Contact Number!", vbInformation, "Contact Number"
            Exit Sub
        Else
            BlnVal = 1
        End If
    End Sub
    

    Step 5: Create function to find lastrow.

    Here is the function code to find last row in the Data Worksheet. Or You can use this function to find last row of any specified sheet by passing sheet name as argument to the function.

    'In this example we are finding the last Row of specified Sheet
    Function fn_LastRow(ByVal Sht As Worksheet)
    
        Dim lastRow As Long
        lastRow = Sht.Cells.SpecialCells(xlLastCell).Row
        lRow = Sht.Cells.SpecialCells(xlLastCell).Row
        Do While Application.CountA(Sht.Rows(lRow)) = 0 And lRow <> 1
            lRow = lRow - 1
        Loop
        fn_LastRow = lRow
    End Function
    

    Step 5.1: Create procedure to clear fields of userform

    Here is the code to clear the Userform fields. It is helpful when we want to update more than one record to the worksheet. Once you add record to the worksheet and then clear data fields to enter new data to on the UserForm.

    'Clearing data fields of userform
    Private Sub cmdClear_Click()
        Application.ScreenUpdating = False
            txtId.Text = ""
            txtName.Text = ""
            obMale.Value = True
            txtLocation = ""
            txtEAddr = ""
            txtCNum = ""
            txtRemarks = ""
        Application.ScreenUpdating = True
    End Sub
    

    Step 5.2: Cretae procedure to update data to the Worksheet.

    Here is the code to add or update data to the Worksheet.

    Sub cmdAdd_Click()
        On Error GoTo ErrOccured
        'Boolean Value
        BlnVal = 0
        
        'Data Validation
        Call Data_Validation
        
        'Check validation of all fields are completed are not
        If BlnVal = 0 Then Exit Sub
          
        'TurnOff screen updating
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
         
        'Variable declaration
        Dim txtId, txtName, GenderValue, txtLocation, txtCNum, txtEAddr, txtRemarks
        Dim iCnt As Integer
        
        'find next available row to update data in the data worksheet
        iCnt = fn_LastRow(Sheets("Data")) + 1
        
        'Find Gender value
        If frmData.obMale = True Then
           GenderValue = "Male"
        Else
           GenderValue = "Female"
        End If
        
        'Update userform data to the Data Worksheet
        With Sheets("Data")
            .Cells(iCnt, 1) = iCnt - 1
            .Cells(iCnt, 2) = frmData.txtName
            .Cells(iCnt, 3) = GenderValue
            .Cells(iCnt, 4) = frmData.txtLocation.Value
            .Cells(iCnt, 5) = frmData.txtEAddr
            .Cells(iCnt, 6) = frmData.txtCNum
            .Cells(iCnt, 7) = frmData.txtRemarks
                  
            'Diplay headers on the first row of Data Worksheet
            If .Range("A1") = "" Then
                .Cells(1, 1) = "Id"
                .Cells(1, 2) = "Name"
                .Cells(1, 3) = "Gender"
                .Cells(1, 4) = "Location"
                .Cells(1, 5) = "Email Addres"
                .Cells(1, 6) = "Contact Number"
                .Cells(1, 7) = "Remarks"
                
                'Formatiing Data
                .Columns("A:G").Columns.AutoFit
                .Range("A1:G1").Font.Bold = True
                .Range("A1:G1").LineStyle = xlDash
                
            End If
        End With
        
        'Display next available Id number on the Userform
        'Variable declaration
        Dim IdVal As Integer
        
        'Finding last row in the Data Sheet
        IdVal = fn_LastRow(Sheets("Data"))
        
        'Update next available id on the userform
        frmData.txtId = IdVal
        
    ErrOccured:
        'TurnOn screen updating
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        
    End Sub
    

    Step 6: Create procedure to unload userform.

    Here is the code to exit from the userform. Or you can click on top right corner of the userofrm.

    'Exit from the Userform
    Private Sub cmdCancel_Click()
        Unload Me
    End Sub
    

    Step 7: Enableing or TurnOn Screen Update and Events at the end of the project.

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    

Final VBA Module Code(Macro):

Please find the following procedures to create Data Entry UserForm project.
Double click on the Userform(FrmData) and add the following code to it.

'Variable Declaration
Dim BlnVal As Boolean

Private Sub UserForm_Initialize()
    'Variable declaration
    Dim IdVal As Integer
    
    'Finding last row in the Data Sheet
    IdVal = fn_LastRow(Sheets("Data"))
    
    'Update next available id on the userform
    frmData.txtId = IdVal
End Sub
Sub cmdAdd_Click()
    On Error GoTo ErrOccured
    'Boolean Value
    BlnVal = 0
    
    'Data Validation
    Call Data_Validation
    
    'Check validation of all fields are completed are not
    If BlnVal = 0 Then Exit Sub
      
    'TurnOff screen updating
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
     
    'Variable declaration
    Dim txtId, txtName, GenderValue, txtLocation, txtCNum, txtEAddr, txtRemarks
    Dim iCnt As Integer
    
    'find next available row to update data in the data worksheet
    iCnt = fn_LastRow(Sheets("Data")) + 1
    
    'Find Gender value
    If frmData.obMale = True Then
       GenderValue = "Male"
    Else
       GenderValue = "Female"
    End If
    
    'Update userform data to the Data Worksheet
    With Sheets("Data")
        .Cells(iCnt, 1) = iCnt - 1
        .Cells(iCnt, 2) = frmData.txtName
        .Cells(iCnt, 3) = GenderValue
        .Cells(iCnt, 4) = frmData.txtLocation.Value
        .Cells(iCnt, 5) = frmData.txtEAddr
        .Cells(iCnt, 6) = frmData.txtCNum
        .Cells(iCnt, 7) = frmData.txtRemarks
      
        
        'Diplay headers on the first row of Data Worksheet
        If .Range("A1") = "" Then
            .Cells(1, 1) = "Id"
            .Cells(1, 2) = "Name"
            .Cells(1, 3) = "Gender"
            .Cells(1, 4) = "Location"
            .Cells(1, 5) = "Email Addres"
            .Cells(1, 6) = "Contact Number"
            .Cells(1, 7) = "Remarks"
            
            'Formatiing Data
            .Columns("A:G").Columns.AutoFit
            .Range("A1:G1").Font.Bold = True
            .Range("A1:G1").LineStyle = xlDash
            
        End If
    End With
‘Display next available Id number on the Userform
'Variable declaration
    Dim IdVal As Integer
    
    'Finding last row in the Data Sheet
    IdVal = fn_LastRow(Sheets("Data"))
    
    'Update next available id on the userform
    frmData.txtId = IdVal
ErrOccured:
    'TurnOn screen updating
    Application.ScreenUpdating = True
    Application .EnableEvents = True
    
End Sub

'In this example we are finding the last Row of specified Sheet
Function fn_LastRow(ByVal Sht As Worksheet)

    Dim lastRow As Long
    lastRow = Sht.Cells.SpecialCells(xlLastCell).Row
    lRow = Sht.Cells.SpecialCells(xlLastCell).Row
    Do While Application.CountA(Sht.Rows(lRow)) = 0 And lRow <> 1
        lRow = lRow - 1
    Loop
    fn_LastRow = lRow

End Function

'Exit from the Userform
Private Sub cmdCancel_Click()
    Unload Me
End Sub

' Check all the data(except remarks field) has entered are not on the userform
Sub Data_Validation()
     If txtName = "" Then
        MsgBox "Enter Name!", vbInformation, "Name"
        Exit Sub
     ElseIf frmData.obMale = False And frmData.obFMale = False Then
        MsgBox "Select Gender!", vbInformation, "Gender"
        Exit Sub
     ElseIf txtLocation = "" Then
        MsgBox "Enter Location!", vbInformation, "Location"
        Exit Sub
    ElseIf txtEAddr = "" Then
        MsgBox "Enter Address!", vbInformation, "Email Address"
        Exit Sub
    ElseIf txtCNum = "" Then
        MsgBox "Enter Contact Number!", vbInformation, "Contact Number"
        Exit Sub
    Else
        BlnVal = 1
    End If
End Sub

'Clearing data fields of userform
Private Sub cmdClear_Click()
    Application.ScreenUpdating = False
        txtId.Text = ""
        txtName.Text = ""
        obMale.Value = True
        txtLocation = ""
        txtEAddr = ""
        txtCNum = ""
        txtRemarks = ""
    Application.ScreenUpdating = True
End Sub

Insert module from the insert menu and add the following code to it.

‘Here is the code to Show Data Entry UserForm 
Sub Oval2_Click()
    frmData.Show
End Sub
‘To clear data columns data on Data Worksheet
Sub Clear_DataSheet()
    Sheets("Data").Columns("A:G").Clear
End Sub

Display Data Entry UserForm on the WorkSheet:

Here are steps to create ‘Data Entry UserForm’ on the worksheet.

  1. Place any shape by clicking on insert menu from illustrations group.
  2. Right click on the shape, selct assign macro.
  3. select the macro name(‘Oval2_Click) from the available list and click on OK button.
  4. Now, go to the Developer tab.
  5. Design Mode should be turned off from the Controls group.
  6. Now, go back to the shape and click on the created shape to see the Data Entry UserForm on the Worksheet in the active Workbook.

Instructions to Execute the Procedure:

You can download the below file and see the code and execute it. Or else, you create new workbook and use the above code and test it. Here are the instructions to use above code.

  1. Open VBA Editor window or Press Alt+F11.
  2. Insert a new module from the Insert menu.
  3. Copy the above procedure and paste it in the newly created module.
  4. You can hit F5 key from the keyboard and you can see the Data Entry UserForm from the Data Worksheet by clicking on ‘Show Data Entry UserForm!’.
  5. Final Outcome of the project:

    Here is the sample screen shot of the entire project output for your reference.

Data Entry Userform

Data Entry Userform

Download the Data Entry UserForm creator – Excel VBA Project<:

Here is the Data Entry UserForm project workbook macro file to explore yourself.

Data Entry userform using Excel VBA

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Last Updated: March 2, 2023

15 Comments

  1. anil kumar December 23, 2015 at 4:58 PM

    its good

  2. Mina December 23, 2015 at 9:38 PM

    Great works. Thanks

  3. Val August 21, 2016 at 1:55 AM

    Hi

    Just wanted to say a massive thank you for this. I managed to expand the form to include more rows and some combo boxes but I would have been unable to do it without your help.

    Thanks so much.

  4. Rohnak February 23, 2017 at 12:11 PM

    Super….

  5. Mike March 2, 2017 at 2:01 AM

    The example file works fine, but I could not recreate a new file using the directions.

  6. Mike March 2, 2017 at 3:08 AM

    Had to create the “Data” sheet in the workbook, and name the “Home” sheet then my form would launch. Was getting an error when clicking on the Open User Form button, and it would not launch.

  7. oglasi March 16, 2017 at 3:29 AM

    I have user form in sheet1. But i would like to insert data in sheet2. How to do this?

  8. Jasny April 6, 2017 at 1:25 PM

    How to program “edit button” to have possibility of edition all items in table? F.e. after pushing ‘Edit’ we chose a ‘name’ and then all text boxes can be editable?

  9. Ruth April 17, 2017 at 12:00 PM

    All seems working, but I’m stuck with adding the first data. When I click the “Add” button the data remains in the userform. I need to click the “Clear” button to add a new entry. On second entry when I click the “Add” button, automatically the data in userforms clears out. Could you please verify how to clear the data when adding it for the first time?

  10. samue tobung March 21, 2018 at 9:58 AM

    Thanks for so much!

  11. Sylvester Yaiuglig September 10, 2018 at 12:03 PM

    Hi there,

    Could you come up with something that will include this items…. for Data Entry

    1. Employee Name
    2. Title
    3. Salary per hour
    4. Address
    5. Phone
    6. Social Security #
    7. Gender
    8. Marital Status
    9. Birth-date
    10. Employed Date

  12. M F Painter January 15, 2019 at 9:05 PM

    Excellent !
    Keep it Up

  13. sumit August 5, 2019 at 4:50 PM

    Thank-You so mach sir.

  14. Ashish July 16, 2020 at 12:35 PM

    I have done everything write but it is showing Run time error 9
    On debugging showing in frmData.Show

  15. shreeram soni November 2, 2020 at 4:02 PM

    very good

Leave A Comment