Fully Automated Data Entry Userform

Fully Automated Data Entry Form In Excel

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

15 COMMENTS

  1. 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

  2. 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, “#,###.##"))

  3. 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

  4. 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.

  5. 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.

  6. 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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here