FLASH SALE: 25% off my Effective Excel VBA course before Saturday 14th September: 👉courses.excelmacromastery.com/courses/Effective_Excel_VBA 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 ruclips.net/user/sgaming/emoji/7ff574f2/emoji_u1f449.png
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.
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
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.
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!
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.
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
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 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!
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!
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
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 😉
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!
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!
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!
This was AWESOME! Some useful tweaks: Instead of adding code for each field individually I used a consistent naming system (txt_VARNAME or cb_VARNAME for the column VARNAME and tb_VARNAME for combobox values) that allows me to just cycle through all the fields and getting/storing/updating the values in a loop. This way, when I make changes in my form, I only need to make sure the naming meets this rule and I won't have to edit the code. Would have loved to see how the code would have to be different for only one form. I guess I will just use the first form to create an empty new line and jump straight into the update form. As a novice in VBA I am still wondering about the differences between public and private subs, and functions, but I guess I'll find that in another video :p Thank's so much for this one!
Clever! I think it would be nice to add to that tutorial one or two levels dependent drop-down list boxes, can you show how to do that in the next tutorial?
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.
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.
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 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.
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
Excellent Tutorial, thanks. Would love to see a Tutorial on data validation in user forms. Do you validate when exiting each control or before the save/update. I have found data validation the most problematic part of coding.
I do data validation through keystrokes and keypress (ascii) at one level. Then second level, I create a function (boolean) which loops through the controls. To identify compulsory fields, I use the tag property of the controls. Not detail enough but I hope it helps to start with.
@@navisalomi Thanks, I like to validate each field as the user exits the control that way when you get to the last control all the data has been validated and you can activate the save button with reasonable confidence most of the unpredictable user behaviour has been controlled! There are many ways to code this but I am yet to find a ‘bullet-proof’ solution. I would love to hear how our excel guru approaches this.
@@johnramsay5386In that case, you can use the "Before update" event by changing the "Cancel" variable to True if d conditions are not met. You can also use d "Exit" event. These events work for each control. If you use a frame. Put the exit event for the frame when the validation depends on other controls. For control interdependencies, when they are in a frame, use the frame's exit event. Summary: try out "before update event" and "exit event". You can google for more clarity.
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👍
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.
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.
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
Hi, After creating modRange it shows me error 424 in modMain in line "frm.Show". Before creating modRange everything was working. I'm at 11:20 and after that it shows me an error. What can I improve?
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.
What if you have a list of 500 items? Editing will take a while as you have to scroll down the list to search for it. Do you have a video of how to add a search box to this form?
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
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.👏
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!
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). 👌
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
Your video is very informative and makes me want to learn more. However, I need instructions about one or two steps below this one to understand the VBA process.
Hi Paul, To get rid of the header, instead of Set getRange = getRange.Offset(1).Resize(getRange.Rows.Count - 1) what's the difference between that and Set getRange = shStaff.Range("A1").CurrentRegion.Offset(1, 0)
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 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 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.
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.
This has been very helpful in my project. Now I need to blend this with a Dependent Forms video I've found so I can make my combo boxes more dynamic. You don't have a video on Dependant lists in UserForms, do you?
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 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 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 this video. I have really only worked with MS Access, and I used VBA a lot. One of the things that I added to my form is to make the background of the current control change color to make it stand out to the user. I put code in the control_Enter Sub to change it, and in the control_Exit Sub to change it back. Is there an auto_order control for tab order?
Paul, great video and content as always. Can you do the same but have the data entry form as a table. Trying to do something similar, get next row in table has its challenges
@@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!! Clear and concise! A question...if others are entering data on a user form like this, is there a way to make the destination sheet itself "read only"?? I only want "write" access for NEW data using the form. Its ok for them to SEE existing data on the sheet (or new data they just entered using the form). But for security, I need to prevent users from going into the sheet outside of the form and potentially changing or deleting any existing information. Here's an example...New members of a group enter their own contact information using a form. They can see the contact information of all others in the group (and their own) at any time. But they can't change change or delete data on other members. THanks!!!
I am making a sort of library file with your youtube video. However, I got a compile error after following all the procedures until 12:34. Can you help me with it?
@ 27:55 ish, when i add the break point to test it out, I test it and the current row selected is always "listindex = 0". What I am trying to do is edit the selected row from my search box. Any suggestions?
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.
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!
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.
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.
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.
This is excellent. I need your help. How can I make data Base form that can allow me to enter marks of different subjects, and find position average, total,and prepare parents report cards automatic?
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
FLASH SALE: 25% off my Effective Excel VBA course before Saturday 14th September: 👉courses.excelmacromastery.com/courses/Effective_Excel_VBA
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
ruclips.net/user/sgaming/emoji/7ff574f2/emoji_u1f449.png
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
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
Thank you
This is almost perfect work. All it needs is Search capability.
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.
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. 🙂
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
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.
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.
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?
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!
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.
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
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.
.....🤗
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!
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.
Excellent tutorial Paul. By far the best approach to user forms I've encountered.
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!
You are wonderful. This is straightforward and finessful coding and explanations. Thank you.
Thanks Will
This was AWESOME! Some useful tweaks: Instead of adding code for each field individually I used a consistent naming system (txt_VARNAME or cb_VARNAME for the column VARNAME and tb_VARNAME for combobox values) that allows me to just cycle through all the fields and getting/storing/updating the values in a loop. This way, when I make changes in my form, I only need to make sure the naming meets this rule and I won't have to edit the code.
Would have loved to see how the code would have to be different for only one form. I guess I will just use the first form to create an empty new line and jump straight into the update form.
As a novice in VBA I am still wondering about the differences between public and private subs, and functions, but I guess I'll find that in another video :p Thank's so much for this one!
Clever! I think it would be nice to add to that tutorial one or two levels dependent drop-down list boxes, can you show how to do that in the next tutorial?
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.
Using the Let and Get methods for Userform properties is a game changer, thanks for sharing this tutorial
Learned a lot. It's great! totally! Just hoping to find a continuation of this video discussing about SEARCH or FILTER features.
The vba codes are much more readable than what I used to do. Great learning! Thank you sir...
You're welcome.
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.
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
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.
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
Excellent Tutorial, thanks. Would love to see a Tutorial on data validation in user forms. Do you validate when exiting each control or before the save/update. I have found data validation the most problematic part of coding.
I do data validation through keystrokes and keypress (ascii) at one level. Then second level, I create a function (boolean) which loops through the controls. To identify compulsory fields, I use the tag property of the controls. Not detail enough but I hope it helps to start with.
@@navisalomi Thanks, I like to validate each field as the user exits the control that way when you get to the last control all the data has been validated and you can activate the save button with reasonable confidence most of the unpredictable user behaviour has been controlled! There are many ways to code this but I am yet to find a ‘bullet-proof’ solution. I would love to hear how our excel guru approaches this.
@@johnramsay5386In that case, you can use the "Before update" event by changing the "Cancel" variable to True if d conditions are not met. You can also use d "Exit" event. These events work for each control. If you use a frame. Put the exit event for the frame when the validation depends on other controls.
For control interdependencies, when they are in a frame, use the frame's exit event.
Summary: try out "before update event" and "exit event". You can google for more clarity.
ruclips.net/video/M0abXuy-UWY/видео.html
Great job Mate!
Working perfectly!
Excellent! I plan t to use this in my next application which I start writing this weekend. Thank you, Paul!
I want to achieve something I think a bit more complex. But this is a good starting point. I’m new to this
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
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!
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... 🙂
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.
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.
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, After creating modRange it shows me error 424 in modMain in line "frm.Show". Before creating modRange everything was working. I'm at 11:20 and after that it shows me an error. What can I improve?
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.
This is excellent. It addressed so many issues I had in the past.
What if you have a list of 500 items? Editing will take a while as you have to scroll down the list to search for it. Do you have a video of how to add a search box to this form?
Thank you for sharing your mastery of VBA. I redid the exercise on my own workbook and it worked 🙂
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
Hi,It's again great coding with reusable codes video with clear video and voice and great presentation.Thank you again !!!
You are welcome!
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.👏
Great tutorial you have made me start loving excel.Thank you Sir.
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!
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). 👌
Thank you, greetings from Indonesia
So many outstanding methods; you are truly a master!!
Thank you! Cheers!
High quality tutorial video. Thanks. Very helpful
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
Perfect explanation 👌 Thanks ❤
Your video is very informative and makes me want to learn more. However, I need instructions about one or two steps below this one to understand the VBA process.
Hi Paul,
To get rid of the header, instead of Set getRange = getRange.Offset(1).Resize(getRange.Rows.Count - 1) what's the difference between that and Set getRange = shStaff.Range("A1").CurrentRegion.Offset(1, 0)
Resize removes the extra blank row.
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 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 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.
Great video! Curious, how would you do multiple tables in the form?
Another good updates could be a [Search] and a [Print] option...
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
Thank you so much for the knowledge from your tutorial, I am a beginner
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.
This has been very helpful in my project. Now I need to blend this with a Dependent Forms video I've found so I can make my combo boxes more dynamic. You don't have a video on Dependant lists in UserForms, do you?
awesome video as always. Best user form approach
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 work, I love it. You asked about other features: a search feature would be good for when working with lots of data.
Great, great, great, so much tips. My code was at least twice as big!
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!
Wonderful! This is very useful, and can be used over and over. Thanks and more Power.
Dear, excellent Tutorial, please a tutorial at the master detail with excel vba. Congratulations.
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?
Thanks so much for this video tutorial. Your explanations and code are awesome. Would you make a video on making a multipage user form ?
Amazing!!! Thank you.
Thank you too!
Awesome tutorial, have watched it several times. Thanks. Can you update the link to the download workbook?
The link if fine. Make sure to turn off any pop up blockers.
Thank you for this video. I have really only worked with MS Access, and I used VBA a lot. One of the things that I added to my form is to make the background of the current control change color to make it stand out to the user. I put code in the control_Enter Sub to change it, and in the control_Exit Sub to change it back.
Is there an auto_order control for tab order?
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?
Paul, great video and content as always. Can you do the same but have the data entry form as a table. Trying to do something similar, get next row in table has its challenges
Did you find a solution? I am having the same problem.
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!! Clear and concise! A question...if others are entering data on a user form like this, is there a way to make the destination sheet itself "read only"?? I only want "write" access for NEW data using the form. Its ok for them to SEE existing data on the sheet (or new data they just entered using the form). But for security, I need to prevent users from going into the sheet outside of the form and potentially changing or deleting any existing information. Here's an example...New members of a group enter their own contact information using a form. They can see the contact information of all others in the group (and their own) at any time. But they can't change change or delete data on other members. THanks!!!
Very nice, lot of new things, will use from this, expecting few more videos on this.
Thanks Mallesh.
I am making a sort of library file with your youtube video. However, I got a compile error after following all the procedures until 12:34. Can you help me with it?
@ 27:55 ish, when i add the break point to test it out, I test it and the current row selected is always "listindex = 0".
What I am trying to do is edit the selected row from my search box.
Any suggestions?
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.
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.
Além de muito bom no que faz e ensina, ainda compartilha seus conhecimentos deixando baixar o projeto. Parabéns..
Hi, Thanks for this detailed file. If i want to make "Edit" on entering a "record number" , how to do that ?
How did you get the column widths so quickly (at 11:05)? I missed a trick there.
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.
hi sir, what if i want only "IT" shows in department column only?
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.
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?
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.
This is excellent. I need your help. How can I make data Base form that can allow me to enter marks of different subjects, and find position average, total,and prepare parents report cards automatic?
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
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?
Is there any way to reaarange the ID column value when deleting a data?
Hi thx for all. Any Idear to change Height of the first Row (header) ?