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:

ControlPropertyValue
UserFormNamefrmData
CaptionUserData
LabelNamelblId
CaptionID
LabelNamelblName
CaptionName
LabelNamelblGender
CaptionGender
LabelNamelblEAddr
CaptionEmail Address
LabelNamelblCNum
CaptionContact Numer
LabelNamelblRemarks
CaptionRemarks
OptionButtonNameobMale
CaptionMale
GroupNmaeg1
OptionButtonNameobFMale
CaptionFeMale
GroupNmaeg1
TextBoxNametxtId
EnabledFalse
TextBoxNametxtName
TextBoxNametxtLocation
TextBoxNametxtEAddr
TextBoxNametxtCNum
TextBoxNametxtRemarks
MultiLineTrue
CommandButtonNamecmdClear
CaptionClear
CommandButtonNamecmdAdd
CaptionAdd
CommandButtonNamecmdCancel
CaptionCancel

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

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!