Fully Automated Data Entry Userform

By
Dilip Tiwari
-
Fully Automated Data Entry Form In Excel
Advertisement

Simple, Dynamic and Fully Automated Form in Excel – Step-by-Step Tutorial

In this tutorial, we will learn how to create data entry form based on User Form in Excel. We will utilize the Excel sheet as database and User Form to enter the data and transfer it to Database sheet.

You will learn

  1. Preparing table for database
  2. Adding Module for function and procedure
  3. Declaring variables and using them
  4. Creating and designing User Form in Excel
  5. Understanding the properties and methods of Textbox, Label, Frame, CommandButton, Combobox and Userform
  6. Creating Validation function with return type
  7. Creating Sub Procedure to handle Add, Search, Edit, Delete and Reset
  8. Debugging and compiling the code
  9. Testing the application

If you are new to VBA and do not know how to write the VBA code then you do not need to worry. Just watch this step-by-step tutorial to learn coding and other parts.

Click on download button to download the Tracker with source code (Part 1 with Add features only). It is completely free!

Click to download

Automated Data Entry Form – Part 2 (with Edit and Delete Features)

In this tutorial, we have extended the previous file with features like EDIT and DELETE features.

Please watch our YouTube Tutorial.

Click on download button to download the Tracker with source code for Part 1 and Part 2. It is completely free!

Click to download

Automated Data Entry Form – Part 3 (with Search and Filter Features)

In this tutorial, we have extended the previous file with features like Search and Filter features.

Please watch our YouTube Tutorial.

Click on download button to download the Tracker with source code for Part 1, Part 2 and Part 3. It is completely free!

Click to download

Automated Data Entry Form – Part 4 (with Input Validation, Dynamic Serial and Print Features)

In Part 4, you will learn how to make serial number dynamic, validate the entries before saving & printing the data and printing the employee details or export to PDF if printer is not installed on your system.

Please watch the Part 4 YouTube tutorials.

Please click on download button to download the file used in Part 4 having Add, Validate, Reset, Search, Edit, Delete and Print features.

Click to download

Automated Data Entry Form – Part 5 (with Full Screen, Zoom and Dynamic Drop-down)

In Part 5, you will learn how to run the Data Entry form in Full Screen and zoom in the controls in the same ratio. Also, we will make Combo-box dynamic so that you don’t need to add elements through VBA code. Just update the department name in Support Sheet and combo-box will get updated automatically.

Please watch Part-5 tutorial.

Please click on below button to download the Excel file.

Click to download
Advertisement
Dilip Tiwari
http://www.thedatalabs.org
Meet Dilip Tiwari, a computer graduate with over 15 years of experience in data handling, automation, dashboard design, visualization, and data storytelling. Dilip is certified in MS Excel, Microsoft Certified Power BI Data Analyst, SQL, Google Analytics, UI Designing, Digital Marketing, and Wordpress technology. He also runs a successful YouTube channel, TheDataLabs, with over 100K subscribers. Dilip is passionate about technology and loves sharing his knowledge. Join him on his exciting journey as he explores the world of data and technology.

RELATED ARTICLESMORE FROM AUTHOR

32 COMMENTS

  2. Dear Sir,
    Am stuck on the part 3 where am having an error mgs and my search is not working.

    iDatabaseRow = ThisWorkbook.Sheets(“Database”).Range(“A” & Application.Rows.Count).End(x1Up).Row

    I have added some some fields more and am still trying to have the search filter to work.

    Regards,

    Jean

    • Hello Jean,

      You have used 1 in place of L (XLUP) in your code. The correct code is as mentioned below.

      iDatabaseRow = ThisWorkbook.Sheets(“Database”).Range(“A” & Application.Rows.Count).End(xlUp).Row

  3. Its a wonderful tutorial.

    Based on this tutorial, I made an entry user form with textbox for entry value in currency. I would like to format textbox entry with thousand separator.

    I made syntax as follow:

    Sub FormatPrice()
    Dim sPrice As Double
    sPrice = frmpForm.txtpPrice.Value
    frmpForm.txtpPrice.Value = format(sPrice, “#,##0”)
    End Sub

    But it did not work.
    Appreciate if you could correct me

    thank you

    • Thanks for your feedback!
      The correct code would be frmpForm.txtpPrice.Value = Format(sPrice, “#,###”)

      If you want to show decimal numbers as well then you need to use IIF function along with Format. Use the below code to show decimal numbers.
      frmpForm.txtpPrice.Value = IIF(Right(Format(sPrice, “#,###.##”),1)=".", Format(sPrice, “#,###”), Format(sPrice, “#,###.##"))

  6. Hi!
    Team The Data Labs thanks for your publishing these kinds of beneficial videos.
    I have sent an email on your provided email ID to regarding the VB work. Please have a look on that and reply accordingly.

    Thanks
    Regards

  7. hello datalabs,

    i am currently using this tutorial and im very thankful for this. and in return may i point out that there is an error on part 4, when u used the sheet “Print” instead of “Database” for verifying duplicates. and also when i corrected that error, i also noticed that when you edit your data, the duplicate verifier stops you from saving the changes. In solution to that problem i made another save button that does not use the verify sub. Again this is very helpful especial for me that is not that good in programming.

  8. This is really great job,

    Is it possible to keep the data base protected and just keep the filter function working. to filter the data base based on some entries.

    This will be very helpful to me as i utilize it in my maintenance activities Log.

  9. Hello,

    Awesome Demo!

    Error keeps calling out the iColumn:

    Application.ScreenUpdating = False

    Dim shDatabase As Worksheet ‘ Database sheet
    Dim shSearchData As Worksheet ‘SearchData sheet

    Dim iColumn As Integer ‘hold selected column # in Database sheet
    Dim iDatabaseRow As Long ‘Store the last non-blank row # available in Database sheet
    Dim iSearchRow As Long ‘hold the last non-blank row # avaialbe in SearchData sheet

    Dim sColumn As String ‘store column selection
    Dim sValue As String ‘hold search text value

    Set shDatabase = ThisWorkbook.Sheets(“Database”)
    Set shSearchData = ThisWorkbook.Sheets(“SearchData”)

    iDatabaseRow = ThisWorkbook.Sheets(“Database”).Range(“A” & Application.Rows.Count).End(xlUp).Row

    sColumn = frmForm.cmbSearchColumn.Value

    sValue = frmForm.txtSearch.Value

    iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range(“C10:AA”), 0) ‘Change, if number of columns change

    If IsError(iColumn) Then
    MsgBox “Yes”
    Else
    MsgBox “No”

    End If

    ‘Remove filter from Databse worksheet

    If shDatabase.FilterMode = True Then

    shDatabase.AutoFilterMode = False
    End If

    ‘Apply filter on Database worksheet

    If frmForm.cmbSearchColumn.Value = “Name” Then

    shDatabase.Range(“C10:AA9999” & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue ‘Change, if number of columns change

    Else
    shDatabase.Range(“C10:AA9999″ & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=”*” & sValue & “*” ‘Change, if number of columns change

    End If

    • >>>>>>> iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range(“C10:AA”), 0) ‘Change, if number of columns change

  10. This is great, I’ve been learning a lot. I tried duplicating this for another tab/sheet but the code breaks and get differnt errors when changing the reference names of “Database” to something else. How can I change the code to use the code name of the sheet instead so hopefully i can reuse this for a new tab/sheet of my own naming?

    For instance from: iRow = [Counta(Database!A:A)] to iRow = [Counta(Sheet2!A:A)]

    or

    iRow = [Counta(MySheetName!A:A)]

    Or

    If iRow > 1 Then

    .lstDatabase.RowSource = “Database!A2:I” & iRow
    Else

    .lstDatabase.RowSource = “Database!A2:I2”

    End If

    To

    If iRow > 1 Then

    .lstDatabase.RowSource = “Sheet2!A2:I” & iRow
    Else

    .lstDatabase.RowSource = “Sheet2!A2:I2”

    End If

    Thanks!

  11. Hi
    Team The Data Labs thanks for your publishing

    I just download a example and i have an error with ColumnWidhths does not match

    Sub Show_Form()

    frmForm.Show

    End Sub

  12. Hi,
    Can you make the same example for entering the data in a table, instead of a defined range. I tried to convert the range in Database sheet to a table, but any new record added is added outside the table.

  13. HI,
    I want help
    I want to know a function in excel which will allow me to understand which dates fall under which week.I am preparing a weekly tracker. Can u please help me

  14. Hi,
    wonderful and great job sir.
    I have questions.
    I have 3 sheet database lst1, lst2, lst3.
    I want to make cmbA to choose from which database I want to search from.
    Then after choosing from cmbA, cmbB list will base on cmbA option.
    Then I will type from search value.
    The search value will appear in lst1 or lst2 or lst3 base on cmbA option.
    How can I do this?
    Thanks in advance!

  15. Hi, first of all thank you for this magnificent tutorial. Would like to know whether can we change the fonts or imply any conditional formatting that can change the colors of the rows in Database1? Thank you

  16. Require your help in calendar part. if i select the date 12-jun-2012 it display 06-Dec-12

    Thanks & regards,

    A.K. Kannan

  17. Hi,

    I can get the delete button to work, but I’m getting ‘Run-time error ‘380’: Could not set the Value property. Invalid property value.’ on the Edit button. By the way thanks for the tutorials, it is exactly what I needed!

  19. I am having a problem with editing the combo boxes in my form. I get ‘Run time error ‘380’. Could not set the Value property. Invalid property value.’ when I select edit. If I comment out the combo box code in the edit button it will work but nothing will show for the combo box data to be able to edit. I’ve been through the tutorials multiple times thinking there was something I have missed but I can’t seem to find anything.

  21. Hi, I am trying to change the code so that it inputs the data on the spreadsheet vertically rather than horizontally. can you help please, i am a bit stuck.

  22. Dear Sir,
    I find your tutorials very helpful. It has helped me do some things I never imagine I could do with excel.

    I’m creating a data register and keep on getting ‘run-time error’ with the ‘edit’ button VBA code.

    Below is the code. When I inactivate the codes with the apostrophe, the code well but of-course with out the possibility of editing the sections I’d like to edit.

    Private Sub cmdEdit_PMR_Click()

    If Selected_List = 0 Then
    MsgBox “No Row is Selected.”, vbOKOnly + vbInformation, “Edit”

    Exit Sub

    End If
    ‘Code to update the value to respective controls

    Dim sGender As String
    Dim oOrigin As String
    Dim rConsent As String

    Me.TxtRowNumber_PMR.Value = Selected_List + 1
    Me.TxtDate_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 1)
    Me.TxtTitle_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 2)
    Me.TxtFirstName_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 3)
    Me.TxtMiddle_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 4)
    Me.TxtSurname_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 5)
    Me.TxtDOB_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 6)

    sGender = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 7)
    If sGender = “Female” Then
    Me.OptFemale_PMR.Value = True
    Else
    Me.OptMale_PMR.Value = True
    End If

    Me.CmbMaritalStatus_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 8)
    Me.TxtOccupation_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 9)
    Me.TxtCoB_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 10)
    Me.TxtHomeAdd_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 11)
    Me.TxtPostalAdd_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 12)
    Me.TxtWorkPh_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 13)
    Me.TxtMobilePh_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 14)
    Me.TxtEmail_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 15)
    Me.TxtERContName_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 16)
    Me.TxtECRelation_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 17)
    Me.TxtECPh_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 18)

    oOrigin = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 19)
    If oOrigin = “Expat” Then
    Me.OptExpat_PMR.Value = True
    Else
    Me.OptPNG_PMR.Value = True
    End If

    Me.CmbPNGRegion_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 20)
    Me.TxtExpatCountry_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 21)
    Me.TxtReligion_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 22)
    Me.TxtAllergichx_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 23)
    Me.TxtMedications_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 24)

    ‘rConsent = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 25)
    ‘If rConsent = “Yes” Then
    ‘Me.OptConsentYes_PMR.Value = True
    ‘Else
    ‘Me.OptConsentNo_PMR.Value = True
    ‘End If

    ‘Me.TxtFileNo_PMR.Value = Me.LstDatabase_PMR.List(Me.LstDatabase_PMR.ListIndex, 26)

    MsgBox “Please make the required changes and click the ‘save’ button to update.”, vbOKOnly + vbInformation, “Edit”

    End Sub

    I don’t know what else to do. Pls help.

LEAVE A REPLY

Please enter your comment!
Please enter your name here