Data Entry Userform

Home/VBA Code Explorer/Projects/Data Entry Userform

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!

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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

LIMITED TIME OFFER

9 Comments

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

    its good

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

    Great works. Thanks

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

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

    Super….

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

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

    • Mike March 2, 2017 at 3:08 AM - Reply

      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.

  6. oglasi March 16, 2017 at 3:29 AM - Reply

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

  7. Jasny April 6, 2017 at 1:25 PM - Reply

    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?

  8. Ruth April 17, 2017 at 12:00 PM - Reply

    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?

Leave A Comment


Related pages


project management templates in excelmonthly gantt chart excel templatenested if then excelwhat are the shortcut keys in ms excelvba to open excel file from folderexcel duplicate rowsvba programming guideexcel vlookup isnaremove duplicates in excelmerge multiple excel files into one sheet vbaselect case vba excelvba codesprotect excel workbook with passwordhow to remove duplicate data in excel 2010vb exit buttonvba update querytimeline examples in excelexcel vba break for looptask tracking spreadsheet templatevba while loopuseful excel templatesqlikview introduction pptinsert check mark in excel 2007copy paste vba excelmacros on excelvba programming in excel 2007infosys interview process for experiencedvba copy sheet to new workbookvb6 questions and answerschange the width of a column in excelloops vbaexcel insert multiple columnsdelete row macrohow to add data validation in excelexcel datevalue functionvlookup basicsexcel 2010 how to merge cellsexcel color index numbersmicrosoft excel trend analysisexcel formulas sumif examplesms excel timeline templateinsert a checkbox in excel 2007ms sql interview questions and answers pdfweekly status report template wordnot iserrorwhat is a vlookup in exceladvanced pivot table in excel 2007offset example excelexcel macro activecellhow to unprotect workbook in excel 2007data analyst interview questions and answers for freshersexcel vlookup alternativevba dynamic arrayfancy graphs in excelpaste in vbacopy folder vbscriptvlookup codeexcel macro introductionsimple vlookup formula in excelformat column vbatextbox in vbaworksheet protection excelfree excel dashboard templates 2010vba xldownexcel formulas and functions examplesbelajar excel macrounprotect sheet excel vbaexporting xml to excelvba formsexcel macro insert rowapplication workbooksexcel macro gotoif functions in excel 2010protecting cells in excel 2007how do i merge two columns in excelexcel dynamic chart rangewriting vba code in excel 2010