How to Create a Data Entry Form in Excel

How to Create a Data Entry Userform in VBA

In this video, I'm going to show you how to create a Data Entry form for your Excel records. You can add, remove and edit records using this form. Let me know in the comments if there are other features that you would like to see on a data entry form.

#VBADataEntry #DataEntryUserForm #ExcelVBAUserForm


Useful VBA Shortcut Keys
========================

Debugging:
Compile the code: Alt + D + C OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)

Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R

Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

[Excelmacromastery]

Hope you enjoy the video. Let me know if you use or plan to use UserForms in the comments below

[hichamhadj]

This is almost perfect work. All it needs is Search capability.

[georgekhaba]

time table :
@1:52 creating the form
@2:48 inserting the list box
@3:48 inserting the command buttons
@6:19 programing the close button
@7:06 inserting a module for the display of the data entry form
@8:19 selecting the delete new and edit button to be programed
@8:40 initializing the form
@9:29 creating a range module
@10:24 creating a link to the list box using row source
@11:30 coding the delete button
@13:23 creating a form for the staff info
@16:24 programing the close button on the new form
@17:16 initialize the userform
@17:51 the create new id function
@18:52 loading combobox with data
@20:32 making full time option is default
@21:35 saving the new data
@22:58 emptying the textboxes
@23:38 writing the data to the worksheet
@26:20 coding the edit staff button
@28:10 reading the data details
@31:58 writing the new updates to the sheet

thank u for the great lesson and i hope the time stamps helps guiding threw the lesson

[brianhayden]

I want to concur with so many of the earlier posters- your coding is exemplary when it comes to efficiency, cleanliness and reuse. By far the best VBA channel I've come across. I only wish I had the time to sign up to your mastery course.

[munimrashid]

Thanks in advance for the promised video! There are many examples of Data Entry Userform in VBA in the net, but when it comes from Paul Kelly, we certainly have some expectations! code reusability, cleanliness, readability and efficiency.

The screenshot looks attractive, and what I would expect is, there is an easy way to populate the dropdowns with options chosen from a ranges that can grow over time.

I would expect, in future you’ll come up with another advanced Userform that enables easy way to incorporate cascading/dependent dropdowns to ensure an upper level of data validation!

[youtubeissatanicscum]

Thanks for the code now it's up to me to learn and modify it for my need. Thanks again.

[azannos]

One if not the best video for forms….amazing. Please consider making another one with only using Listobjects (tables). Maybe with header-detail scenario? Keep up the GREAT job you are doing!

[vcondos]

Amazing video. For an amateur programmer like myself there is so much information to think about, presented in such a clear and compact way. Thank you!

The modular structure and the cleanliness of the code, offers so much food for thought for on how to use it and adapt it in various situations. Every step in the video taught me one new thing, and got me thinking about how I've been doing things, or about possibilities on how to improve my coding approach in various areas.


Building applications mostly for own use, I always thought of user forms as unnecessary, or more accurately restrictive, believing that data entry/manipulation is generally faster doing it directly in the various areas where the information is kept. Having been rethinking lately of how to improve an ever growing excel "database" in terms of data and scope, which is reaching certain limits, I've been looking for things here and there to get ideas on how to apply some extra structuring and to decide whether to introduce some code in an application that I was keeping purposely with minimum makros and mostly relying on tables, names, pivots, etc. After going through this video, I am actually starting to think of introducing user forms even in this application which I wanted to keep with as little code as possible and with as much as little structured data entry.

This is how inviting this video has been for me.

Oh and actually, as someone asked about putting out an extra video that applies this code to a structure involving only tables, well, this video is so good and the code is so well structured, that I've actually very easily modified it to try it out in a structure with tables.

[ElectromecanicaIndustrial]

Using the Let and Get methods for Userform properties is a game changer, thanks for sharing this tutorial

[knut]

Another masterpiece in a series of outstanding VBA videos! Asking what could be next: adding a filter (there are various possibilities…only one column…multiple columns….change of listbox as u type) and a print function (sorry woods) would be great.
Thx again Paul….and tell Microsoft VBA is the future. They have created a jewel

[russthomas]

I agree with so many of other people. This was a huge help to me as I'm a civil engineer and make use of VBA to keep me from repeating mundane tasks. I'm looking forward to watching more of your content.

If you take questions... how would I place a filter in the listbox you created? Thanks!

[munimrashid]

An EXTRAORDINARY tutorial in many different ways, enabling us to brainstorm for extending it further to meet our practical needs. Thank you indeed Paul, for leading us to that direction!

[zakimoustapha]

I like the way you link your listbox to the sheet. It's a tricky way and really super important. You are the best teacher I've ever seen but still your book is expensive 😉

[rukhbrw]

Hi Mr Paul, I'm beginner in vba and knows very less in vba. After seeing your video, it even cleared to me that you gave exceptional skills in vba

[bobbyterupo]

Excellent tutorial Paul. By far the best approach to user forms I've encountered.

[richardbaron]

I'm going to create a new record user form with combo boxes to speed up data entry of my emails WIP sheet (I can edit records directly in the table, so don't need the intermediate form). This code is very clean and easy to understand - many thanks!

[danfoltz]

Thanks for sharing. I have wanted to do this in previous uses but never got it all figured out. You make it look so easy. I will certainly give it a shot.

[beu]

Very helpful! Thanks a lot. I will definitely update my own solution.
You asked about possible further features to add. Would be cool to see:
- filter Staff list by field data (e.g. only show people from a specific country) and still be able to edit an entry
- mark more than one person in the list (select via mouse click or a specific field content) and do an action with the selection (e.g. send an E-Mail)
- can some list rows be in a different color depending on a field data (e.g. person is on holiday and not available at the moment)
Thanks again for your effort and all the learnings I have from it!

[barangaytuliao]

Learned a lot. It's great! totally! Just hoping to find a continuation of this video discussing about SEARCH or FILTER features.

[PerezerepL]

I'm grateful for your channel. Thanks for your teachings. I will be using this UserForm and this will guide me to make some modifications that will suit my actual need, which is that I would like to be able to add multiple rows of data entry 'before' hitting the save button. Regards.