How to Create a Data Entry Form in Excel
HTML-код
- Опубликовано: 4 июн 2024
- 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
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
DOWNLOAD THE SOURCE CODE FOR THIS VIDEO: bit.ly/3bCUnDk
Related Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
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) 
Наука
Hope you enjoy the video. Let me know if you use or plan to use UserForms in the comments below
Enjoyed it very much, will be referring to it often. I am absolutely in awe of the tiny procedures. Thank you.
Yes, I am making it, replacing my existing process. You make me do stuffs. This approach is certainly safer and cleaner than my current one. But my current method is already quite complex with conditional validation, searches, formulas. I need to test it more thoroughly before I can confirm I will use this.
thank you ... any video about digital signature certificate for vba
Great video. still waiting to receive code to download the file
I will use it to capture heavy load services, it would be interesting to know how you do the save to customize the Userform, with the information that is in the Settings sheet
This is almost perfect work. All it needs is Search capability.
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
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.
Excellent tutorial Paul. By far the best approach to user forms I've encountered.
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!
Using the Let and Get methods for Userform properties is a game changer, thanks for sharing this tutorial
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!
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!
Glad you like it Munim.
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). 👌
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.
This is excellent. It addressed so many issues I had in the past.
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!
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.
Thank you for sharing your mastery of VBA. I redid the exercise on my own workbook and it worked 🙂
Great job Mate!
Working perfectly!
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!
ruclips.net/video/a8MV_J2aTN0/видео.html
And not to mention he speaks clearly and I like his Irish accent. 🙂
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!
Glad it was helpful Karl!
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.
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.
You're welcome Karl.
Perfect explanation 👌 Thanks ❤
High quality tutorial video. Thanks. Very helpful
Learned a lot. It's great! totally! Just hoping to find a continuation of this video discussing about SEARCH or FILTER features.
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
The vba codes are much more readable than what I used to do. Great learning! Thank you sir...
You're welcome.
Thank you, greetings from Indonesia
Wonderful! This is very useful, and can be used over and over. Thanks and more Power.
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!
Excellent! I plan t to use this in my next application which I start writing this weekend. Thank you, Paul!
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.👏
You are wonderful. This is straightforward and finessful coding and explanations. Thank you.
Thanks Will
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
i appreciate and thanks again. i spent maybe 3 hours with your video and create a useful and basic program for my stuff. You are great. do u have udemy education? i believe i ll learn much more. thanks thanks again
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.
Thank you so much for the knowledge from your tutorial, I am a beginner
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!
So many outstanding methods; you are truly a master!!
Thank you! Cheers!
I want to achieve something I think a bit more complex. But this is a good starting point. I’m new to this
Helpful tip - run this video at 50% speed to have a chance of following what’s happening.
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... 🙂
I Found Very Very Useful. Thanks.
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!
Thanks a lot Wayne. Glad you liked it.
Great, great, great, so much tips. My code was at least twice as big!
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
Thanks, glad you like it.
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
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
awesome video as always. Best user form approach
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.
Great to hear!
Great work, I love it. You asked about other features: a search feature would be good for when working with lots of data.
Great, love this. Certainly planning to use a version of this myself.
Thanks Graham.
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.
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!
Thanks Fritz, those are good suggestions.
Hi,It's again great coding with reusable codes video with clear video and voice and great presentation.Thank you again !!!
You are welcome!
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
There is a delete item check in the code download.
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
Thank you for this Video.
Very informative video, I've learnt a lot from this. Thanks 😌
You're welcome Paul.
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
Thanks for the suggestions
Thanks so much for this video tutorial. Your explanations and code are awesome. Would you make a video on making a multipage user form ?
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 😉
Yes book expensive.
.....🤗
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?
As always, amazing
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.
Glad you found it useful.
Excellent, thanks!
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!!!👍😀
Great to hear!
Amazing!!! Thank you.
Thank you too!
Thank You Very Much
Great video once more.
How would you approach sorting the Tables before populating the ComboBoxes ?
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.
exactly what I was looking for! How come it didn't work using shStaff?
Hi Paul, thank you for the great video ... there is way to edit text alignment for individual columns in the listbox using RowSource?.. Thank you!
I love you so much, thank you so much!
Thanks a lot it looks very easy and nice
Great video! Curious, how would you do multiple tables in the form?
You did an amazing job.Thank You so much, I love your youtube channel. I wish you all the best.
Best regards,Andrew
Thanks Andrew
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.
thanks, i learned alot!
Very nice, lot of new things, will use from this, expecting few more videos on this.
Thanks Mallesh.
Loved the video. Is it possible to create the form using listview and still have the ability to edit the entries? I like the grid lines of list view and hoping to incorporate it.
Dear, excellent Tutorial, please a tutorial at the master detail with excel vba. Congratulations.
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?
great tutorial.
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👍
Thanks Hadi
gracias maestro!
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
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.
Like the spreadsheet table of form Settings you keep. Do you have code to store / write the userform settings or must it all be done by hand?
That was very helpfull!
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.
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
I like it. Like to see of such stuff.
Another good updates could be a [Search] and a [Print] option...
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
Hi, Thanks for this detailed file. If i want to make "Edit" on entering a "record number" , how to do that ?
Thank you for providing such a great video :) does this form allow multi-user to save the form at the same time?
Thank you for the video.
I still have a question, how can I import and save data to listbox from an external .csv file?
very helpful
nicely done, how can you update the list form if the dataset changed after you open it say from another user?
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!😉😁🤟😎
Thanks John. Glad you like it.
@@Excelmacromastery Yeah, I do! It's a good help for new-bes and those, who thinks that's magic! I hope a lot of people will watch this vid!😉
Edit:
Of course more experienced can learn some new things!😇 E.g.: Iif I never used yet, it will shorten my code in future!
Great video, as always. Populating the Edit Staff Member form on *activate*, though, seems very problematic. If I start editing a record, then I Alt+Tab to another window to check something, then I Alt+Tab back, I'll silently lose all my unsaved edits.
Hi thx for all. Any Idear to change Height of the first Row (header) ?
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.
Glad it was helpful! This video(ruclips.net/video/gkLB-xu_JTU/видео.html) contains the code for the searchable dropdown.