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
- Preparing table for database
- Adding Module for function and procedure
- Declaring variables and using them
- Creating and designing User Form in Excel
- Understanding the properties and methods of Textbox, Label, Frame, CommandButton, Combobox and Userform
- Creating Validation function with return type
- Creating Sub Procedure to handle Add, Search, Edit, Delete and Reset
- Debugging and compiling the code
- 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!
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!
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!
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.
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.
It is no free because it is no possible to change and add some items to the code.
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).RowIts 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, “#,###.##"))When the data is saved from part 5 it displays a run time error.
Hello, could you please share the line of code where you are getting error. Thanks!
Thanks for your tutorial Part 5 is having error ” .cmbDepartment.clear “. Please take a look at it again and advise accordingly. Thanks.
Thanks for highlighting the issue! Please remove this line of code.
.cmbDepartment.clearIt will start working.
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
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.
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.
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
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!
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
Please share the line of code where error is showing. Thanks!
Hi,
I think he means in the Module1 at Z124. I got the same Error
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.
Please see the below post. Thanks!
https://thedatalabs.org/data-entry-form-with-excel-table-and-vlookup/
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
Do you have the list of week? Please share here so that I can suggest you the function. Thanks!
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!
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
Require your help in calendar part. if i select the date 12-jun-2012 it display 06-Dec-12
Thanks & regards,
A.K. Kannan
Please change the format while assigning the date to variable. Format should be “DD-MMM-YYYY”
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!
Hi, how to add 1 another option button and change selection from 2 options to 3 please?
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.
Please ignore my last 2 comments regarding errors, I have managed to work out the problem!
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.