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:

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

Final Outcome of the project:

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

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