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