+WiseOwlTutorials I second that, Vineet! WiseOwl, I have very quickly expanded my knowledge of VBA in the past few months and watching your tutorials is a huge reason for that. Thank you!
Dear Andrew, Once again, thank you so much for this great lesson and helpful tips. Although video posted almost 8 years, but until now, its value is truly necessary for new learner.
Great. Crystal-clear and straightforward explanations. And the good impression you get the first time around can only improve when you watch it again, This is clearly a valuable resource in teaching the fundamentals of VBA. I would even go so far as to say that the more you advance in your knowledge of VBA, the more you appreciate the quality of this teaching method. Highly recommended. Don‘t deprive yourself of it!
Dear Andrew, I can not believe how you can know all these things of VBA. The hypotheses are two: 1) it's been a long time since you study this subject and have a lot of experience; 2) you're a genius! I think the second is right! Thank you so much for these videos.
Hi Andrew, I just wanted to join everyone else here and say how grateful I am for your tutorials. I've been watching these lessons since July of 2018, about 5 months now...I have an extremely busy work schedule, so unfortunately in 5 months I was only able to go through 21 lessons so far, but these lessons have helped me so much at work! I have implemented so many changes to my current department, based on your video tutorials. Because of your fantastic teaching skills, I will be finally getting my VP title at work! Again, thank you very much for all your effort!
For Efficient Reference: What You'll Learn 0:00:12 What are User Forms? 0:00:42 Creating a User Form 0:02:32 Modifying Properties of a From 0:03:33 Adding Controls to a Form 0:05:21 Running a User Form 0:09:15 Using Additional Controls 0:09:50 The Date and Time Picker Control 0:11:56 Spin Buttons 0:15:27 Combo Boxes 0:17:35 Aligning and Distributing Controls 0:20:25 Using Frames to Group Controls 0:21:55 Adding Code to a Form Event 0:23:36 Changing the Tab Order of Controls 0:29:28 Unloading a Form 0:32:06 Loading and Showing a Form 0:35:07 Linking a Textbok to a Spin Button 0:39:20 Locking and Disabling Controls 0:43:10 Linking a Spin Button to a Text Box 0:45:24 Validating a Single Text Box 0:46:23 Checking the Range of a Spin Button 0:49:55 Validating Multiple Controls 0:52:06 Validating a Combo Box 0:55:55 Validating a Date and Time Picker 0:57:48 Restricitng the Values in a Combo Box 1:02:14 A Summary 1:03:58
Your videos are just perfect. I went from knowing nothing to being able to read basic vba in a week. thank you, thank you, thank you. Look forward to many more videos from you. your a true professional.
I love how you started with error 1 of spin box values and gave 5 solutions and then 10 different solutions for each of the errors caused by each of those solutions and then another solution of a solution of a solution. Ok, exaggerating but you get the point. Thank you very much!!!
Dear Andrew, I watch and watch again and again your videos at any time of the day and whenever I can and I never get tired of appreciating and admiring your great ability to teach so easily what would seem difficult to learn. You are truly exceptional! Sincere congratulations!
Andrew, You did it again. Excellent. Thank you. I finally understood how to start with UserForm in VBA. Hope you do not stop here!! You are definitely gifted to convey this subject to your audience and I encourage you to pursue this skill. It is not easy to find professionals like you. I ( and many of your followers) really appreciate your time. Cheers.
One of the best Vba course around. No question about it. Very well structured and always with a reassuring voice leaving no room for doubt that this can all be learned in due course! I haven‘t watched all of the videos yet but I certainly do intend to.
I have learned so much watching your videos in just a short time. I tried other teachers on youtube but found yours to the best, easy to understand, very practical. I started from part 1 and now I am in part 21. I had to stop just to give a comment. can't wait for my next project. Great work!
Really useful and best tutor ever I seen...thank you very much. It is very helpful for needy person who is not able to follow a cause for any reasons. thank you again
Great teaching style, i like the way you have explained and used all the things in one single vba app. Your voice guide is also very helpful and informative. keep it up.
Your videos courses are realy realy awsome. I just need to say that the way in which you presented and share your knowledge is perfect. thank you very very... very much
that you explained very well, may be you like this part. everyone loves to play with tools and drawings. anyway this one I loved it. Makes me believe working with VBA. thumbs up for this one.
Well, I've gotten to this point, and I'm loving how much I now know and understand! :) This has been one long but thorough learning process. Thanks for being so clear!
Hi Andrew Thanks for fascinating turorials on Excel VBA. In Pt5 and Pt21 when I tried your prog trying to enter info from scratch into a blank spreadsheet, I ran into terrible problems with the code crashing. As a bit of a learner and after much experimenting, I found that it was trying to reach the very last row +1 ! It appeared that there had to be at least 2 cells in A1 already filled in, as in your example, then it would work. Thanks again and I will carry on with rest of tutorials.
Another great video can use some of the coding and tips in my own userforms. Can you add to next userform video how to fill combox based on values in previous combox or set text box based on choice in combox. Keep up the good work.
This is great. Thanks so much. Can you do something where you show how to create: 1: LIST BOX 2:SELECT MULTIPLE DATA POINT 3: CREATE A RECORD FOR EACH ONE DATA POINT SELECTED. Something like having 3 movies at the same time having a unique ID row for each movie.
Hi Andrew, good day. I have a question. On a userform i have combo and text boxes in a row, there are 8 rows. I have made controls in each row a group (mainly for formating purpose). On run time i hide/make invisible rows 2-8, and once the user click on check box at the end of rows 1-7, next row will become visible for entering data. Idea is to use it as a data entry grid. Question: Is there a way through which i can make visible / invisible the group that i made, instead of making visible / invisible the individual controls in each row? Your help shall help me to complete my task. Thanks.
Hi there! I think the easiest way to do this is to add each row of controls into a separate Frame control. You can then change the frame's visible property to show/hide all of the controls in it. You can remove the value from the Caption property of the frame to make the form look a little cleaner. An alternative technique would involve using the Tag property of each control in the row. Set the value of the Tag for each control on a row to have the same value, for example "Row2". You can then loop over the Controls collection on the form when you check/uncheck the Checkbox on row 1, test if the Tag property is equal to "Row2" and hide/unhide the control as required. If you wanted more of a challenge you could even create your controls programmatically! docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/add-method-microsoft-forms stackoverflow.com/questions/3014421/how-to-add-events-to-controls-created-at-runtime-in-excel-with-vba I hope that helps!
Hello. Thank you for your videos, they are very helpfull. I have one suggestion, though - adding something like a content table in the description. As your tutorials are in parts it would be great, if you could easily switch to the exact part you need. Best regards
Once again, amazing tutorials. I am useing mac excel 2011 and I am unable to add more controls to the User Form Tool box. How do I add, right clicking does not do anything, searched on internet but not found anything. How do I add a date picker?
Dear Andy, after hours of investigation, hope that I found the cause of strange behavior. I have Win10 and by the default Window Defender as antivuris. When I switch it off, it works perfect (excel open fastter and without any dissapearing of userform's). With some other more advanced antivirus program it also work. When I switch on default Windows Defender again, after 3-4 times of opening excel, began the same problem. Anyway, thanks for the advice and on everything you do for us a poor knowledge in vba. THANK'S
I have spent several hours trying many different methods to install the date picker app into Excel 2010 64-bit version and have failed. I've completed this tutorial (masterful!) and my remaining issue is this. I have used a txtReleaseDate for the date box and the validation setting isn't recognizing and entry as a date. I think I need to Dim a variable to handle this issue but have not worked out quite where... to set the variable. Am I wrong in this line of thinking and... any guidance you may provide would be appreciate. Thanks again for putting these videos out there! I have high hopes that, in the very near future, I will be in a position to express my appreciation!
I have tried alternatively tried adding txtReleaseDate.Text = Format(Date, "mm/dd/yyyy") and then change it to txtReleaseDate.value = Format(Date, "mm/dd/yyyy") in the to the UserForm_Initialize() sub... neither worked for me. Commenting out the validation code for txtReleaseDate works as Sheet1[Release Date] turns txtReleaseDate.value into a date, no problem.
Hi there! You can check if a value is a valid date using the IsDate function like this: If Not IsDate(Textbox1.Value) Then MsgBox "Not a date" Exit Sub End If We have a video which explains that in more detail which you can see here (it uses an Inputbox rather than a user form but the idea is the same) th-cam.com/video/vP4_hJkjxvo/w-d-xo.html I hope it helps!
Excellent Videos! It helps me a lot!. Thanks for take your time to do them. Also I would like to know where can I find the Excel Files that you use on this videos?
Thanks 1 million!!.. for taking me from Excel user stage to the VBA ”programmer” in 10 days. Know you are busy with the developing of your site. I know each corner and each inch of your tutorial material but after some 5 full time working days I am starting to sweat a bit…. Apart from VBA I have tried out data filter, consolidation, lookup functions and much more… but… have still not found the answer. I am using your Film tutorial video and use a userform to enter 5 values: 1. Room 2. M2 3. Product type 4. Product hight 5. Product width Each Room (/row) has 5 calculated values: Room 1 | m2 | Product type | Product hight | Product width | Value 1 | Value 2 | Value 3 | Value 4 | Value 5 | Room 1 | m2 | Product type | Product hight | Product width | Value 1 | Value 2 | Value 3 | Value 4 | Value 5 | Room 1 | m2 | Product type | Product hight | Product width | Value 1 | Value 2 | Value 3 | Value 4 | Value 5 | Room 2 | m2 | Product type | Product hight | Product width | Value 1 | Value 2 | Value 3 | Value 4 | Value 5 | Room 3 | m2 | Product type | Product hight | Product width | Value 1 | Value 2 | Value 3 | Value 4 | Value 5 | My challenge is, that each room (/row) can have a more than 1 product - example: Room 1 has 3 products and I am asked to show both the table above listing each product AND a summarized table where each room is summarized - like this: Room 1 | Sum m2 | Sum value 1 | Sum value 2 | Sum value 3 | Sum value 4 | Sum value 5 | Room 2 | Sum m2 | Sum value 1 | Sum value 2 | Sum value 3 | Sum value 4 | Sum value 5 | Room 3 | Sum m2 | Sum value 1 | Sum value 2 | Sum value 3 | Sum value 4 | Sum value 5 | Is there a report function in VBA or what am I missing out on? Can you help? Even if you can’t - so many thanks for taking me to where I am today - compared to last week! Carsten
Thanks a lot for videos and probably the best place to learn excel and vbas in youtube. For me the spin button is working the opposite way. When I click down button value increases and vice versa. Could u please advise how to fix it
Andrew, You're amazing! I have been watching your videos to learn VBA..Wanted to know if you sell VBA /Access DVD's..Would like to buy one...Seriously wish to marry your brains :)..Thank you WiseOwl!
***** Hi Andrew How do I go to a specific cell that is inputted by user? For instance, I want to find a value, select that cell, stay in that row and fill all the left and right column(fields) of that specific row. I tried to use .offset. Please Advise. Your help is appreciated. Thank you
***** Hi Andrew, Thank you so much for the reply. yeah. I am was aiming to go to specific cell and populate data on the offset's of that cell. I think i got that part. But major issue is i want populate data without opening the file. Is it possible? Thank you
***** Hi Andrew, Thank you once again. But this is an excel file that is used by a lot user. And is ADO connection the only way to do that cause i don't think i can use that since this is a corporate computer? Thank you
Hi Andrew, loved watching this video. Your delivery method is excellent, as I'm rarely able to watch an hour-long video and stay captivated and informed all the way through!! One quick question, with regard to the 'Genre' combo box, I like the ability to lock down this field to only permit the user to use those values defined in the range. However, on occasion it will be necessary to add a new value to the permissible combo box. If I were to add this in the range values (in your separate worksheet), would the VBA code detect the new value and add this to the combo box list? Cheers, Dean
Wise Owl, Love your training lessons. I'm trying to get a text box to input the data as a number. As in this video, how would I have the film length placed in the spreadsheet as a number? Any help would be great!
how can you change the property of the film length textbox, so that it saves the text as a number in your spreadsheet rather than as text. New films added now have the lengths aligned to the left of the column rather than the right. Thanks very much!
Thank you Marcel, glad you enjoyed it! There was no particular reason to choose an ActiveX button here, a Form Controls button would have worked equally well. I think an ActiveX button works better in the context of this video as its properties and methods are the same as the buttons we can draw on the user form. I hope that helps!
Hi Andrew, great video, love it. I have one question, i have formatted the inputted values as table. if the table is empty ( no rows) i am able to input 1 row with id 1 though next records will overwrite row 1 contents of the table. Reason for formatting the inputted values as table is because this will be easier for me to operate a pivot table. Thanks a lot. wim
Your videos are by far some of the best tutorials for anything I've had the pleasure of going through. Quick question regarding the date picker. If the file gets sent to users that need to utilize the file, do they also need to have the MSCOMCT2.OCX pack installed on their machines? also if you have office 64bit, how would you use a date picker since you mention it cant be registered? many thanks in advance. B
Thank you for the Tutorial! It helps! Could you please give the list of libraries installed for this Tutorial? These libraries are in VBA Editor, Tools->References.Thank you.
Hi, thank you SO much for all these tutorials, you are truly the best! I would have the following question: I have 2 ComboBoxes, cboName and cboAge. Furthermore, I have one TextBox, txtSpeed. What I now want is that, as soon as I choose two specific things in cboName and cboType, let's say "John" and "20", that the TextBox, txt Speed, becomes visible on the UserForm. Otherwise, I want the TextBox to be invisible. I am really stuck here as I don't even know which Private Sub function (Click, Change, AfterUpdate...) I need to use. You think you could help me on this one? Much appreciated! Cheers
Really Amazing Tutorials!!!Benefit me a lot Just want to point out that the link of the Name Conventions mentioned at the earlier part of the tutorial has failed. Does Microsoft use another way to deal with the name conventions? Thank you
Hello. Thank you for sharing. I have been following your tutorial, but I have run into some problems. At first after adding code to the save button, the userform would freeze everytime I tried to play it. I redid it all over again, but now I get an error in the code that says "cannot jump to "ActiveCell" because it is hidden" I am not sure what does that mean and how to fix it. I am sorry if this question is very basic, but I am fairly new at this. I am absolutely sure there were no errors in copying the code. I am using excel 2010. any advice will be welcomed. Thank you.
Yeh...! there is no additional control " mscomct2", I tried to download, but not. My desktop computer's o.s is windows 7. Even I couldn't get the filled popup window for additional controls. I am confused now
Fantastic tutorial, but let me ask a stupid question. When I run the code, I get a runtime 1004 error at the Range("A1").End(xlDown).Offset(1, 0).Select line. The code works without the line (so long as I have manually put the cursor at the right place, of course, which is problematic). The code worked on another machine a few months ago, but isn't now (either on Excel 07 or 13; I think Windows 7 on the former and 10 on the latter, 64bit in both cases). Thoughts?
@@WiseOwlTutorials I've followed the tutorial and set up a similar database for practice, so I have the 'add record' button in the excel spreadsheet that opens up the input form which is great. I've created the 'save record' button and the 'cancel' one. However, I'd like to have the option on the input form itself to 'add another record' instead of cancelling out and then having to open up the form with blank fields.
Hi Andrew, thank you for the video, it does help a lot for what I'm doing for work. I have a question. i'm trying to do "if " statements but with two conditions. Let's say Condition1 is certain number, and Condition2 is certain number, than do something. I'm a newbie on Microsoft Excel VBA.
***** thank you Andrew, that video was very helpful. I'm truly sorry on bothering you again (I will try not to this very often) . I had created a user form, and loaded the data, and most of them I had set the parameter. How can i automatically validate the data with the parameters given.
Could you help me how to create serachable dropdown using combox in userform for example if I type ABC then all the contents should be visible in a list that contains ABC Thanks in advance
You are The Best Tutor. I have no word to tell you how your video helped me learning VBA.
+WiseOwlTutorials I second that, Vineet! WiseOwl, I have very quickly expanded my knowledge of VBA in the past few months and watching your tutorials is a huge reason for that. Thank you!
Andrew, You're not a good teacher ...
You're a great teacher !
Its so rare to find something this complex,explained so simply. thank you very much!
Dear Andrew,
Once again, thank you so much for this great lesson and helpful tips.
Although video posted almost 8 years, but until now, its value is truly necessary for new learner.
Thank you! The nice thing about VBA is that it really hasn't changed in the past 8 years!
this VBA course is of Legendary level, thanks to anyone who participated in creating it, to Andrew "the Teacher" Gould
in particular
Thanks so much! Happy to hear that you've enjoyed the tutorial and thank you for taking the time to leave a comment!
There is always science and arts to anything you do. You have simply mastered both of them without doubt. No words really!
you are a bloody legend man. The best Excel videos I have found anywhere (after much searching)
cheers to you and your loved ones
Great. Crystal-clear and straightforward explanations. And the good impression you get the first time around can only improve when you watch it again, This is clearly a valuable resource in teaching the fundamentals of VBA. I would even go so far as to say that the more you advance in your knowledge of VBA, the more you appreciate the quality of this teaching method. Highly recommended. Don‘t deprive yourself of it!
Dear Andrew, I can not believe how you can know all these things of VBA. The hypotheses are two:
1) it's been a long time since you study this subject and have a lot of experience;
2) you're a genius!
I think the second is right! Thank you so much for these videos.
Hi Andrew,
I just wanted to join everyone else here and say how grateful I am for your tutorials. I've been watching these lessons since July of 2018, about 5 months now...I have an extremely busy work schedule, so unfortunately in 5 months I was only able to go through 21 lessons so far, but these lessons have helped me so much at work! I have implemented so many changes to my current department, based on your video tutorials. Because of your fantastic teaching skills, I will be finally getting my VP title at work! Again, thank you very much for all your effort!
For Efficient Reference:
What You'll Learn 0:00:12
What are User Forms? 0:00:42
Creating a User Form 0:02:32
Modifying Properties of a From 0:03:33
Adding Controls to a Form 0:05:21
Running a User Form 0:09:15
Using Additional Controls 0:09:50
The Date and Time Picker Control 0:11:56
Spin Buttons 0:15:27
Combo Boxes 0:17:35
Aligning and Distributing Controls 0:20:25
Using Frames to Group Controls 0:21:55
Adding Code to a Form Event 0:23:36
Changing the Tab Order of Controls 0:29:28
Unloading a Form 0:32:06
Loading and Showing a Form 0:35:07
Linking a Textbok to a Spin Button 0:39:20
Locking and Disabling Controls 0:43:10
Linking a Spin Button to a Text Box 0:45:24
Validating a Single Text Box 0:46:23
Checking the Range of a Spin Button 0:49:55
Validating Multiple Controls 0:52:06
Validating a Combo Box 0:55:55
Validating a Date and Time Picker 0:57:48
Restricitng the Values in a Combo Box 1:02:14
A Summary 1:03:58
Thank you Thomas!
Such as prefect Teacher, you do know what we will ask in the same time as you demonstrate the steps
Your videos are just perfect. I went from knowing nothing to being able to read basic vba in a week. thank you, thank you, thank you. Look forward to many more videos from you. your a true professional.
As others have said, these are definitely the best source for excel in my opinion! Thank you so much for the time you have taken to do these videos!
I love how you started with error 1 of spin box values and gave 5 solutions and then 10 different solutions for each of the errors caused by each of those solutions and then another solution of a solution of a solution. Ok, exaggerating but you get the point. Thank you very much!!!
Dear Andrew, I watch and watch again and again your videos at any time of the day and whenever I can and I never get tired of appreciating and admiring your great ability to teach so easily what would seem difficult to learn. You are truly exceptional! Sincere congratulations!
You are the man !!! Straight to the point with no extra bla, bla...
Andrew,
You did it again. Excellent. Thank you. I finally understood how to start with UserForm in VBA. Hope you do not stop here!!
You are definitely gifted to convey this subject to your audience and I encourage you to pursue this skill. It is not easy to find professionals like you.
I ( and many of your followers) really appreciate your time.
Cheers.
One of the best Vba course around. No question about it. Very well structured and always with a reassuring voice leaving no room for doubt that this can all be learned in due course! I haven‘t watched all of the videos yet but I certainly do intend to.
Excellent video with a superb teaching methodology that allows for easy following along and learning. Thank you.
I have learned so much watching your videos in just a short time. I tried other teachers on youtube but found yours to the best, easy to understand, very practical. I started from part 1 and now I am in part 21. I had to stop just to give a comment. can't wait for my next project. Great work!
Really useful and best tutor ever I seen...thank you very much. It is very helpful for needy person who is not able to follow a cause for any reasons. thank you again
Dear Andrew, you're just a genius! No other words are needed!
Your lectures helped me to automated some of the monthly financial reports. Thank you so much for sharing the knowledge.
This is an incredibly instructive, easy-to-follow, and detailed video that I have seen! Thank you!
greatest vba serie ever, undisputed! thanks a lot
I was not able to use Excel VBA Userforms: i watched your video and now I CAN. Thanks man
Great video. Very in-depth and covers a load of different possibilities. Thanks so much for posting.
Your teaching contents and methods are very good. keep this spirit.
Great teaching style, i like the way you have explained and used all the things in one single vba app. Your voice guide is also very helpful and informative. keep it up.
Your videos courses are realy realy awsome. I just need to say that the way in which you presented and share your knowledge is perfect. thank you very very... very much
Thank you, good Sir. I thought watching your videos will serve as a refresher, but I managed to found some new stuff so... another thanks for that!
that you explained very well, may be you like this part. everyone loves to play with tools and drawings. anyway this one I loved it. Makes me believe working with VBA. thumbs up for this one.
You are one of the wisest owls I know!
Just plain dam good format and structure . Sharing lots of great info........ TY Salamat Po
Thanks Tony!
I really appreciate your videos. I am greatful to you because now I know what VBA is and how to create commands. Thank you.
More power to you.!
Extremely enjoyable and a good learning outcome, keep up the good work.
Thank you, Robert!
Well, I've gotten to this point, and I'm loving how much I now know and understand! :)
This has been one long but thorough learning process. Thanks for being so clear!
Thank you, Userforms are something I've always avoided before - but this has made it look easy :)
BRAVO MONSIEUR. Vous êtes un vrai pédagogue. MERCI
THANK YOU, OMG ITS 4AM AND IM DOING PROJECT TO MY COLLEGE AND YOU ARE MY HERO
ok, the best thing about this video was the twilight movie genre "awful" hahaha
Andrew, thanks for this amazing tutorial!
this is the best vba video i've ever watched. thanks for sharing!!!i like it
Such an excellent video; great pace and clearly explained. 10/10!
Another excellent video sir, I have learnt so much from watching your video series - keep it up!
Great video! I always try to keep up with you without pausing the video. I need to have a cigarette now
Excellent, well done!
Another fantastic instructional video...thank you Andrew!!
***** Hi Andrew, is it possible to make the calendar default to today's date? Mine is stuck on 3/19/2015
Excellent, Perfectly paced and just what I wanted. Thanks.
Hi Andrew
Thanks for fascinating turorials on Excel VBA. In Pt5 and Pt21 when I tried your prog trying to enter info from scratch into a blank spreadsheet, I ran into terrible problems with the code crashing. As a bit of a learner and after much experimenting, I found that it was trying to reach the very last row +1 ! It appeared that there had to be at least 2 cells in A1 already filled in, as in your example, then it would work. Thanks again and I will carry on with rest of tutorials.
Amazing Video! Great, Clear, and Very Helpful Explanations!
Thanks again always helpful. Keep up the good work looking forward to seeing more of your videos.
Two big thumbs up. Thanks Andrew.
Best Excel videos ever! Thank you so much
Another great video can use some of the coding and tips in my own userforms.
Can you add to next userform video how to fill combox based on values in previous combox or set text box based on choice in combox.
Keep up the good work.
Great tutorial, just what iI was looking for.
This is great. Thanks so much. Can you do something where you show how to create:
1: LIST BOX 2:SELECT MULTIPLE DATA POINT 3: CREATE A RECORD FOR EACH ONE DATA POINT SELECTED. Something like having 3 movies at the same time having a unique ID row for each movie.
Great video, as usual. Thank you!
Really really good presentation my dear
Thanks Ravi, glad you enjoyed it!
Hi Andrew, good day. I have a question. On a userform i have combo and text boxes in a row, there are 8 rows. I have made controls in each row a group (mainly for formating purpose). On run time i hide/make invisible rows 2-8, and once the user click on check box at the end of rows 1-7, next row will become visible for entering data. Idea is to use it as a data entry grid.
Question: Is there a way through which i can make visible / invisible the group that i made, instead of making visible / invisible the individual controls in each row?
Your help shall help me to complete my task. Thanks.
Hi there! I think the easiest way to do this is to add each row of controls into a separate Frame control. You can then change the frame's visible property to show/hide all of the controls in it. You can remove the value from the Caption property of the frame to make the form look a little cleaner.
An alternative technique would involve using the Tag property of each control in the row. Set the value of the Tag for each control on a row to have the same value, for example "Row2". You can then loop over the Controls collection on the form when you check/uncheck the Checkbox on row 1, test if the Tag property is equal to "Row2" and hide/unhide the control as required.
If you wanted more of a challenge you could even create your controls programmatically! docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/add-method-microsoft-forms
stackoverflow.com/questions/3014421/how-to-add-events-to-controls-created-at-runtime-in-excel-with-vba
I hope that helps!
Hello. Thank you for your videos, they are very helpfull.
I have one suggestion, though - adding something like a content table in the description. As your tutorials are in parts it would be great, if you could easily switch to the exact part you need.
Best regards
Once again, amazing tutorials.
I am useing mac excel 2011 and I am unable to add more controls to the User Form Tool box. How do I add, right clicking does not do anything, searched on internet but not found anything. How do I add a date picker?
Very nice work! Thank for help, you are the best! ;)
Very instructive. Thanks a lot!
Good video - Thanks for your help!
Dear Andy, after hours of investigation, hope that I found the cause of strange behavior. I have Win10 and by the default Window Defender as antivuris. When I switch it off, it works perfect (excel open fastter and without any dissapearing of userform's). With some other more advanced antivirus program it also work. When I switch on default Windows Defender again, after 3-4 times of opening excel, began the same problem.
Anyway, thanks for the advice and on everything you do for us a poor knowledge in vba.
THANK'S
I have spent several hours trying many different methods to install the date picker app into Excel 2010 64-bit version and have failed. I've completed this tutorial (masterful!) and my remaining issue is this.
I have used a txtReleaseDate for the date box and the validation setting isn't recognizing and entry as a date. I think I need to Dim a variable to handle this issue but have not worked out quite where... to set the variable.
Am I wrong in this line of thinking and... any guidance you may provide would be appreciate.
Thanks again for putting these videos out there! I have high hopes that, in the very near future, I will be in a position to express my appreciation!
I have tried alternatively tried adding txtReleaseDate.Text = Format(Date, "mm/dd/yyyy") and then change it to txtReleaseDate.value = Format(Date, "mm/dd/yyyy") in the to the UserForm_Initialize() sub... neither worked for me.
Commenting out the validation code for txtReleaseDate works as Sheet1[Release Date] turns txtReleaseDate.value into a date, no problem.
Hi there! You can check if a value is a valid date using the IsDate function like this:
If Not IsDate(Textbox1.Value) Then
MsgBox "Not a date"
Exit Sub
End If
We have a video which explains that in more detail which you can see here (it uses an Inputbox rather than a user form but the idea is the same) th-cam.com/video/vP4_hJkjxvo/w-d-xo.html
I hope it helps!
very very good. Thank you so much for sharing!
Excellent Videos! It helps me a lot!. Thanks for take your time to do them.
Also I would like to know where can I find the Excel Files that you use on this videos?
Thanks 1 million!!.. for taking me from Excel user stage to the VBA ”programmer” in 10 days.
Know you are busy with the developing of your site.
I know each corner and each inch of your tutorial material but after some 5 full time working days I am starting to sweat a bit….
Apart from VBA I have tried out data filter, consolidation, lookup functions and much more… but… have still not found the answer.
I am using your Film tutorial video and use a userform to enter 5 values:
1. Room
2. M2
3. Product type
4. Product hight
5. Product width
Each Room (/row) has 5 calculated values:
Room 1 | m2 | Product type | Product hight | Product width | Value 1 | Value 2 | Value 3 | Value 4 | Value 5 |
Room 1 | m2 | Product type | Product hight | Product width | Value 1 | Value 2 | Value 3 | Value 4 | Value 5 |
Room 1 | m2 | Product type | Product hight | Product width | Value 1 | Value 2 | Value 3 | Value 4 | Value 5 |
Room 2 | m2 | Product type | Product hight | Product width | Value 1 | Value 2 | Value 3 | Value 4 | Value 5 |
Room 3 | m2 | Product type | Product hight | Product width | Value 1 | Value 2 | Value 3 | Value 4 | Value 5 |
My challenge is, that each room (/row) can have a more than 1 product - example: Room 1 has 3 products and I am asked to show both the table above listing each product AND a summarized table where each room is summarized - like this:
Room 1 | Sum m2 | Sum value 1 | Sum value 2 | Sum value 3 | Sum value 4 | Sum value 5 |
Room 2 | Sum m2 | Sum value 1 | Sum value 2 | Sum value 3 | Sum value 4 | Sum value 5 |
Room 3 | Sum m2 | Sum value 1 | Sum value 2 | Sum value 3 | Sum value 4 | Sum value 5 |
Is there a report function in VBA or what am I missing out on?
Can you help? Even if you can’t - so many thanks for taking me to where I am today - compared to last week!
Carsten
Thanks a lot for your advice! All the best!
C’est passionnant. Merci. Amicalement
Great video series thank you !
Thanks a lot for videos and probably the best place to learn excel and vbas in youtube.
For me the spin button is working the opposite way. When I click down button value increases and vice versa. Could u please advise how to fix it
Thank you so much for sharing your knowledge
Andrew, You're amazing! I have been watching your videos to learn VBA..Wanted to know if you sell VBA /Access DVD's..Would like to buy one...Seriously wish to marry your brains :)..Thank you WiseOwl!
Excellent video.Thanks a lot for it
*****
Hi Andrew
How do I go to a specific cell that is inputted by user? For instance, I want to find a value, select that cell, stay in that row and fill all the left and right column(fields) of that specific row. I tried to use .offset. Please Advise. Your help is appreciated. Thank you
***** Hi Andrew, Thank you so much for the reply. yeah. I am was aiming to go to specific cell and populate data on the offset's of that cell. I think i got that part. But major issue is i want populate data without opening the file. Is it possible? Thank you
***** Hi Andrew, Thank you once again. But this is an excel file that is used by a lot user. And is ADO connection the only way to do that cause i don't think i can use that since this is a corporate computer? Thank you
Thank you so much! the lecture is very well-explained. Just wondering are we able to download the excel files that you have presented?
Hi Andrew, loved watching this video. Your delivery method is excellent, as I'm rarely able to watch an hour-long video and stay captivated and informed all the way through!!
One quick question, with regard to the 'Genre' combo box, I like the ability to lock down this field to only permit the user to use those values defined in the range. However, on occasion it will be necessary to add a new value to the permissible combo box. If I were to add this in the range values (in your separate worksheet), would the VBA code detect the new value and add this to the combo box list?
Cheers,
Dean
Wise Owl, Love your training lessons. I'm trying to get a text box to input the data as a number. As in this video, how would I have the film length placed in the spreadsheet as a number? Any help would be great!
Great tutorial, thank you!!!
how can you change the property of the film length textbox, so that it saves the text as a number in your spreadsheet rather than as text. New films added now have the lengths aligned to the left of the column rather than the right. Thanks very much!
Great Video
Thanks!
Jeez you're amazing you are! Thanks love x
Hi Andrew, great video! Are there any reasons why you choose an ActiveX Controls button instead of a Form Controls button? Thank you.
Thank you Marcel, glad you enjoyed it! There was no particular reason to choose an ActiveX button here, a Form Controls button would have worked equally well. I think an ActiveX button works better in the context of this video as its properties and methods are the same as the buttons we can draw on the user form. I hope that helps!
Hi Andrew,
great video, love it.
I have one question, i have formatted the inputted values as table. if the table is empty ( no rows) i am able to input 1 row with id 1 though next records will overwrite row 1 contents of the table.
Reason for formatting the inputted values as table is because this will be easier for me to operate a pivot table.
Thanks a lot.
wim
Your videos are by far some of the best tutorials for anything I've had the pleasure of going through.
Quick question regarding the date picker. If the file gets sent to users that need to utilize the file, do they also need to have the MSCOMCT2.OCX pack installed on their machines?
also if you have office 64bit, how would you use a date picker since you mention it cant be registered?
many thanks in advance.
B
Thank you for the Tutorial! It helps!
Could you please give the list of libraries installed for this Tutorial?
These libraries are in VBA Editor, Tools->References.Thank you.
Hi, thank you SO much for all these tutorials, you are truly the best!
I would have the following question:
I have 2 ComboBoxes, cboName and cboAge. Furthermore, I have one TextBox, txtSpeed. What I now want is that, as soon as I choose two specific things in cboName and cboType, let's say "John" and "20", that the TextBox, txt Speed, becomes visible on the UserForm. Otherwise, I want the TextBox to be invisible.
I am really stuck here as I don't even know which Private Sub function (Click, Change, AfterUpdate...) I need to use.
You think you could help me on this one?
Much appreciated!
Cheers
Really Amazing Tutorials!!!Benefit me a lot
Just want to point out that the link of the Name Conventions mentioned at the earlier part of the tutorial has failed.
Does Microsoft use another way to deal with the name conventions?
Thank you
Hi Andrew
Amazing!
Hello. Thank you for sharing. I have been following your tutorial, but I have run into some problems. At first after adding code to the save button, the userform would freeze everytime I tried to play it. I redid it all over again, but now I get an error in the code that says "cannot jump to "ActiveCell" because it is hidden" I am not sure what does that mean and how to fix it.
I am sorry if this question is very basic, but I am fairly new at this.
I am absolutely sure there were no errors in copying the code. I am using excel 2010. any advice will be welcomed. Thank you.
You have a gift !
Twilight - genre: awful
I love these touches
Thank you so much for these videos
Yeh...! there is no additional control " mscomct2", I tried to download, but not. My desktop computer's o.s is windows 7. Even I couldn't get the filled popup window for additional controls. I am confused now
Your cross checking procedure is incredible. How do u think😊
Fantastic tutorial, but let me ask a stupid question. When I run the code, I get a runtime 1004 error at the Range("A1").End(xlDown).Offset(1, 0).Select line. The code works without the line (so long as I have manually put the cursor at the right place, of course, which is problematic). The code worked on another machine a few months ago, but isn't now (either on Excel 07 or 13; I think Windows 7 on the former and 10 on the latter, 64bit in both cases). Thoughts?
+WiseOwlTutorials: Thanks! I was getting the same error. But why was I getting the error in the first place?
Hi Andrew, good day. Do you have any detailed tutorial for using listview with excel? I searched your videos but couldnt find one.
Hi! Sorry no, we don't currently have a video on the ListView control.
Hi - Great tutorial, really helpful but could do with instructions on how to add a 'new record' button - any suggestions please?
@@WiseOwlTutorials I've followed the tutorial and set up a similar database for practice, so I have the 'add record' button in the excel spreadsheet that opens up the input form which is great. I've created the 'save record' button and the 'cancel' one.
However, I'd like to have the option on the input form itself to 'add another record' instead of cancelling out and then having to open up the form with blank fields.
Hi Andrew, thank you for the video, it does help a lot for what I'm doing for work. I have a question. i'm trying to do "if " statements but with two conditions. Let's say Condition1 is certain number, and Condition2 is certain number, than do something. I'm a newbie on Microsoft Excel VBA.
***** thank you Andrew, that video was very helpful. I'm truly sorry on bothering you again (I will try not to this very often) . I had created a user form, and loaded the data, and most of them I had set the parameter. How can i automatically validate the data with the parameters given.
I forgot to mentioned... I had tried to use While loop to make it automated, but it keep freezing on me so I'm guessing that is not the right command.
Could you help me how to create serachable dropdown using combox in userform for example if I type ABC then all the contents should be visible in a list that contains ABC
Thanks in advance
Is there away of seeing what properties and methods each UserForm object has ?
Can somebody explain me, when do we use exit sub statement? And why did we use it in SaveFilm command button code?