How to Create a Data Entry Form in Excel 

Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/ Hope you enjoy the video. Let me know if you use or plan to use UserForms in the comments below

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

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

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.

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!

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.

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

You have some great video's. If I can give you a little constructive criticism, you should pan out and pause for a few seconds before switching windows (So we can see all of the code). I'm trying to follow along and write the code along with the video. I will be writing out a line and all of a sudden you switch screens. I have to back the video up or catch the code the next time you switch back to that screen. I am a beginner and I'm not as fast as you. Keep the videos coming.

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

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!

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!

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.

I needed to create a form for Stock Management for my colleague, and this video was perfect talking me through my first VBA project. I will say I tripped several times with not being able to download the source code (Company blocked), however extremely happy with how it's turned out! Will be watching more of your videos!

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

Paul, that's fantastic. It encourages me to change my previous user forms. I see a lot of potential for improvement for me, as well as very simple and structured programming. Thank you very much for this. I am looking forward to more examples.

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

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!

Great job Mate! Working perfectly!

Hi Paul. This is awesome! Full of great tips and techniques. I like the way you separate the code into logically connected components vs. writing it all behind the buttons. Studying this in depth and learning more each time through! Thanks for sharing :)) Thumbs up!! PS - Also like your formatting choices which give a more modern look!

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.

Yes, excellent! Wanted to echo several others who would like to see how you approach FILTERING (particularly for apps with hundreds or thousands of rows, perhaps with a ListObject). 👌

What an amazing tutorial. I tackled this myself and the lines were probably five times as long. I learn so much from you, thank you!

Excellent! I plan t to use this in my next application which I start writing this weekend. Thank you, Paul!

This is excellent. It addressed so many issues I had in the past.

Thank you for sharing your mastery of VBA. I redid the exercise on my own workbook and it worked 🙂

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

This is just the thing I was looking for, perfect. Even if it going a little bit fast, (nice with pause) it's a great video. Thumbs up... 🙂

Wonderful! This is very useful, and can be used over and over. Thanks and more Power.

High quality tutorial video. Thanks. Very helpful

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!

Paul is the BEST 👌 teacher of VBA. Thank you so much for your hard work & source code for us. Liked & definately Subscribed . Love ❤the tutorial. Looking forwarrd to more.👏

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

Perfect explanation 👌 Thanks ❤

Thank you, greetings from Indonesia

awesome video as always. Best user form approach

Great work, I love it. You asked about other features: a search feature would be good for when working with lots of data.

I Found Very Very Useful. Thanks.

This was a good tutorial. I did get tripped up a few times following along but the included worksheet showed me the deviation so I thank you for that. To take this further, refreshing the list in real time for adds and removes would have been cool. I could see how another list on the modfiy and edit page would give that update view real time... "people you just added" list. and when close that would be present on the initial list. personally i would like to see role and group selection UI with the forms. thats where i am hoping to build some functionality with forms. thanks for the video

Great, great, great, so much tips. My code was at least twice as big!

Great tutorial as always. Thanks a bunch. I have a suggestion though. Rather than have two different forms do the "Add New Staff" and "Update existing staff". One form can do that. Just change the "Save button caption" depending on "new mode" or "edit mode". Same sub for saving new staff can be used for existing staff. The difference would be the "row number". New staff row num is Next Avail row. Existing staff: row offset from listbox.

Great, love this. Certainly planning to use a version of this myself.

I'm in the process of integrating this tutorial for my needs and in the interim I learned about ListView Controls. I researched ListView and would like your opinion on the differences why one is better than the other. Excellent tutorial!

You are wonderful. This is straightforward and finessful coding and explanations. Thank you.

The vba codes are much more readable than what I used to do. Great learning! Thank you sir...

Thank you so much for the knowledge from your tutorial, I am a beginner

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 😉

This was a really helpful video and I used it as a guide to make a data entry form for tracking failure events at wind farms. I would like to modify it to include some data validation such as making sure certain text boxes only allow dates or integers. It would be great to have a similar walkthrough of that.

As always, amazing

Thank you for this Video.

Thanks so much for this video tutorial. Your explanations and code are awesome. Would you make a video on making a multipage user form ?

Excellent, thanks!

I love you so much, thank you so much!

So many outstanding methods; you are truly a master!!

You did an amazing job.Thank You so much, I love your youtube channel. I wish you all the best. Best regards,Andrew

Hi,It's again great coding with reusable codes video with clear video and voice and great presentation.Thank you again !!!

Thank you in advance for such a useful theme. I guess it would be very good if you explain how to enter / delete / change data via Data Entry Form for linked lists.

at the 13th minute, i can say, you are perfect sir. Thanks so much. this is the first time i have ever seen such a perfect way describing codes. Appreciate

Helpful tip - run this video at 50% speed to have a chance of following what’s happening.

I like it. Like to see of such stuff.

That was very helpfull!

Amazing!!! Thank you.

Your tutorial is very straight forward and easy to follow. Before I go through all of these steps (which I will for practice at some point), does this cover multi-instance records using indexes to be able to use entry into multiple worksheets / tables? Thanks

I want to achieve something I think a bit more complex. But this is a good starting point. I’m new to this

thanks, i learned alot!

Great video!!! I’ve done loads of application for users using vba but I do like to get to know how others deal with vba, their way of developing user forms etc. I like your video a lot. Impressing!!!👍😀

Thank you Paul for share your huge knowdlegees with us humans beans. I'd like to set a check after had clicked the delete button just in case to confirm that the record must be delete. Thank you again

gracias maestro!

great tutorial.

Very informative video, I've learnt a lot from this. Thanks 😌

Very nice, lot of new things, will use from this, expecting few more videos on this.

Thanks a lot it looks very easy and nice

Thank You Very Much

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!

Hi, you have made an excellent video. You could add: How to do it now with a ListView? How to add a Filtered range to the listbox or ListView? Thank you very much

I couldn't get shStaff to actually reference the Staff worksheet so I've had to replace it with Sheets("Staff"). Otherwise, so far so good! Great video.

I thought I knew everything in vba ... just discovered the accelerator property 😂 so used to the VB "&" placed in the caption and was wondering why it wasn't implemented in vba lol... Thanks Paul, I love your videos👍

I'm new to Excel VBA. Found this extremely helpful - thank you. The content covered nearly all the aspects I was looking for apart from being able to Search using a cell for Name or Telephone number as examples. Also I would like the ID number to be populated but not editable by the user. Will start digging to see what other Excel Macro Mastery solutions can help me.

Dear, excellent Tutorial, please a tutorial at the master detail with excel vba. Congratulations.

Another good updates could be a [Search] and a [Print] option...

This was an amazingly helpfull video! Thanks a lot. I did notice 1 mistake though (which cost me a bit of frustration): at 24:50 in the iif statement you put true between quotes. This doesn't work but doesn't give you an error message ether. Instead it just defaults to the False statement.

Paul, I see, you spent much time for this video! But it's a great vid for learning that GUI stuff! Thanks for all your YT content!😉😁🤟😎

Hi Paul. Great video. You asked for suggestions to improve the user form: 1) Maybe validation to make certain the user does not make a duplicate entry. 2) maybe add prev/next arrows to scroll through Listbox. Thanx

Great video (and site). Very professional code. Thanks for sharing. Some remarks: Loved the way a userform can be duplicated. If user is allowed to select any cell in the “Staff” worksheet, to indicate the record to be updated or deleted, “New”, “Edit” and “Delete” buttons could be put directly in this worksheet, with the same functionalities. Thus, “formStaffList” could be skipped. (Albeit, in this case, this video, too.) There are tutorials that discourage the usage of RowSource property, in favor of the List one. Probably because of Excel bug(s).(?) There is a bug in “CreateNewID” method. If the last record is deleted, and a new one is added afterwards, the new one takes the Id of the deleted one. Instead, perhaps the last used Id could be stored in a cell of the workbook, and its value be incremented by one, whenever a new record is created. Again, thanks for your services.

very helpful

Great video once more. How would you approach sorting the Tables before populating the ComboBoxes ?

Awesome content. This improves my userforms remarkably. Clean code and very responsive, compare to my current one. Did u ever experienced Run-time error '-2147024882 (8007000e) errors at the rowsource properties? Running 32GB, how there are not enough ressources, Microsoft hello? Sometimes it appears and I do not know why. btw? a list-view version would be interesting, offers so much functions..

you are Gooood

Paul, Great stuff again. I am doing something with this but rather than a worksheet being the data source I am using an array. Any tips on expanding this method to an array would be helpful. Jon

Great video! Curious, how would you do multiple tables in the form?

very good！

Thanks for the video! If you’ve made certain columns named ranges, can you extend the named range (automatically) when you enter a new row of data?

Hi, I found your video and lost it so looked at others. Yours is by far the best and easiest to understand and adapt. I've subscribed and can't wait to see more. One questions: can you have a lookup with predictive text? Eg., we have a list of client contacts, we'd like the sales people to be able to start typing the first name (say Andrew) and start seeing all the Andrews in the list. Then have the corresponding client details populate automatically.

Bravo

The best

Great tutorial! Wondering, is there a technical reason you use functions and arrays outside of the Userform to populate your combo boxes instead of Rowsource?

Thank you for providing such a great video :) does this form allow multi-user to save the form at the same time?

Thank you very much for sharing another amazing video... I just wonder why you did not use listobjects instead of range for the Staff record?

Hi I'm work on a project and I learned a lot from your tutorial. As par of on going project, can you teach us how to create a search using a combo box as a criterion and when you enter the data that you want to search in the text box search the results will display in the list box and text box

You are the best of the best. But what I didn't like is to double the userform. You could use another method just to catch if the form was open for save mode or édit mode. Please help us to create a class for validation controls so we don't need to write each time the same code

Very nice - simple clear structure. I would be interested to see how you would structure data validations - for example, suppose you wanted to ensure that the employee name was unique. Could I suggest that for working with larger databases it would be useful to use the currently selected record in the database as the highlighted record when displaying the list. So for example if the user has selected record ID 5 in the Staff worksheet, that you would highlight that record in the list when you run data entry (rather than going back to the first record). As a further suggestion (pushing my luck here) handling filtered data would be of interest. Keep up the good work. Corbett