How to Create a Multi-User Data Entry Form in Excel (Step-by-step Guide)

Multi-User Data Entry Form in Excel
Multi-User Data Entry Form

If you are looking for Excel based Data Entry form with multi-users functionalities then this post is going to be very helpful for you. In this post, we will discuss User Interface (UI) design and VBA coding to develop the Multi-user Data Entry Form.

Why do we need to develop Multi-user Data Entry Form in Excel?

Microsoft Excel is one of the most utilized tool in any company. Due to availability and dynamic features of Excel, we can use this tool to automate any complex data entry operation. There is no need to hire programmers and install a separate language. MS Excel has an  inbuilt integrated development environment (IDE). Visual Basic for Applications (VBA) is the programming language for Microsoft Office and its associated applications e.g. Excel, Word, Power Point etc. We can leverage the power of VBA language and develop the multi-user data entry form without any additional cost.

In this Multi-User Data Entry form, we have two separate Excel files. One is fulfilling the purpose of Database and other is for Data Entry Form to transfer the data to database workbook.

To use this tool, you just need to keep the parent folder at any shared location and ask your team and users to open the Data Entry Form and start submitting the data. Excel VBA code will handle all the required functionalities e.g. accessing database, validating entry, transferring inputs etc.

Creating Multi-user Data Entry Form in Excel

Follow the below steps to create this Automated Data Entry form in Excel.

  1. Create a folder named ‘Multi-user Data Entry Form’ at the shared drive.
Create Folder
Create Folder

2. Create a new Workbook and save the file with the name ‘Database.xlsm” in ‘Multi-user Data Entry Form folder’.

Create Database File
Create Database File

3. Rename the ‘Sheet1’ to ‘Database’ and add the below mentioned column headers and format the the headers accordingly in ‘Database.xlsm’ file.

Database Headers
Database Headers

4. Create a new Workbook and save the file with the name ‘Multiuser Data Entry Form in Excel.xlsm’ in ‘Database’ folder.

5. Rename the ‘Sheet1’ to ‘Home’. Remove the Gridlines ( View – Gridlines) and add a rounded rectangle and fill the color with Blue and enter the text ‘Data Entry Form’. Please see the below image.

Data Entry Form Button
Data Entry Form Button

6. Now go to Develop Tab and click on Visual Basic to visit Visual Basic Application window.

Develop Tab
Develop Tab

7. In Visual Basic for Application window, click on Insert menu and then select ‘UserForm’.

Add UserForm
Add UserForm

8. Set the properties of the userform and add required controls. Add label, texboxes, image control, option button, combobox and command button as per below image.

Labels: Employee Registration Form (Top headers), Name, Date of Birth, Gender, Qualification, Mobile Number, Email ID, Address

TextBox: Name – txtName; Date of Birth – txtDOB; Mobile Number – txtMobile; Email-txtEmail; Address:txtAddress

Image: Calendar- imgCalendar

Option Buttons: Gender – Female – optFemale; Male-optMale

Combobox: Qualification- cmbQualification

Command Buttons: Submit- cmdSubmit; Reset-cmdReset

Multi-User Data Entry Form in Excel
Multi-User Data Entry Form

9. Set the Tab order for each and every cotnrols.

10. Import the custom calendar from the support file. Link has been provided at the bottom of the page.

11. Insert a module and rename it to ‘mdDataEntry’.

Add Module
Add Module

12. In Module code window, start writing the code to handle Reset, Validate Email, Validate Fields, Transfer Data and Call Form.

Code to Reset the Form

Code to Validate Email ID

Code to Validate Data Entry by User

Code to Transfer the Data from Form to Database

Code to show the UserForm

13. Let’s write the coding on Form Initialization, Command Button events and Image click events.

Code on Form Initializtion

Code on click events for Calendar image

Code for click events on Submit and Reset buttons

14. Move to Excel window and assign the macro on ‘Data Entry Form’ button available on Home sheet.

Right click on Button, select ‘Assign Macro’ and select ‘Show_Form’ as macro name and click on ‘OK’.

Now, Multi-User Data Entry form is ready. You can ask your team members to start using this tool to submit the data in shared environment.

Please watch our YouTube tutorial to learn how to create Multi-user Data Entry form in Excel (step by step).

Click here to download the Multi-user Data Entry Form.

Click here to download the support files (calendar icon, custom calendar form and email code) used in tutorial .

Please visit the below mentioned pages to learn more about Data Entry Forms and Automated Tools in Excel. Thanks!

Annual Leave Tracker

Employee Activities Tracker in Excel

Data Entry Form

Advanced Data Entry Form in Excel

Fully Automated Data Entry Form

LEAVE A REPLY

Please enter your comment!
Please enter your name here