Excel VBA Forms Part 5 - Validating Forms

[@hubertg7100]

Can't flatter you enough Andrew. these tutorials get better w/age , I want to loop to not just to the tutorial in the Excel VBA class object but the WiseOWlTutorials class and inner loop all members of that class and give it each tutorial a Big Thumbs Up! Thank you for your endless dedication and unimaginable time that you spend creating these contents rich outstanding tutorials!

[@WiseOwlTutorials]

Thank you so much Hubert! Very pleased to hear that you've enjoyed the videos - it's comments like this that make the effort worthwhile!

[@tedjammers]

These are so excellent. Thank you so much for your time, expertise, and effort to make these great!

[@patricialamas429]

I agree

[@JanBolhuis]

Amazing, Amazing Amaz...... What a ton of value. Thank you very much!

[@WiseOwlTutorials]

Dankjewel Jan!

[@alexeiofp]

thanks you master, I'm late watching ur videos but never is late when u want to learn from the best master.

[@WiseOwlTutorials]

Thanks Alexis, hope you found it useful!

[@dedeoliveira82]

Olá! Sou do Brasil. Tenho acompanhado esta série de vídeos do seu canal e tem sido muito útil ao meu aprendizado. Obrigado por compartilhar o conhecimento. A didática é ótima.

[@AhmedEjam1]

You Must Enter the Paradise ! Thanks A lot I have a small suggestion for next videos please, could you increase the size of viewed screen and zoomed so can fit the full screen (there is more blank black color around the video!)

[@pluginguin]

Hello WiseOwl, Cudo's for the very clear and complete video series. Very helpfull and instructional! I have two questions regarding this video. 1. For the textboxes to return to their original state once a valid value is entered, there is no other option than to put code in their own afterupdate subs? 2. The setfocus bit you showed at the end of the video causes a small problem in my userform: There is one texbox which is considered last in the "AnythingMissing" boolean loop, but is put in second place in the form. (reason is that I added that texbox after all the others). With the tab-index I made it second tab for users, but the loop doesn't see that. So if that textbox remains empty and there is another textbox a bit lower in the form also empty, that one will get the focus from the "AnythingMissing" boolean loop. How can I solve that?

[@ysz10000]

Hey wiseowl, you are awesome

[@bruce2357]

Something I like to do when typing variables names, control names and keywords, is use all lower case letters, not when naming them. but when typing them later in the code. This alerts me to an issue if the letters that should be uppercase remain lower case. For example when you used control + h to replace "Name" with "Gross", I would type "gross" in lower case and after replacing them all I would expect to see all the replacements with the initial upper case g. If I made a mistake and typed "grosss", then I would see my error right away as I would be seeing "Filmgrosss" instead of the expected "FilmGross", maybe someone else would find this technique useful. I like your idea of control + spacebar, but I've been a touch typist for approximately 40 years so it's faster for me a lot of times just to type the known variable, control name or keyword.

[@Arunsharma-rv7lp]

Hi Sir, I have really learnt a lot from your vba userform tutorial. I want to ask can we partially unload the form. Like I want to save the data of when the userform last used. but once I unload the userform, If I have not paste it in my excel sheet. the data lost. Does VBA has the method to store some data in userform that can be retrived??

[@WiseOwlTutorials]

Hi Arun! No, not as far as I know. You'll need to write the data somewhere in order to retrieve it into the form after unloading and reloading it. You can use hidden or very hidden worksheets if you don't want your user to see this information. I hope that helps!

[@abo3621]

Hello WiseOwl, First of all I want to thank you for this brilliant work. I have watched all of your vba series and they are extremely useful. I have a question: I have a userform that consists around 25 labels. I have made a everythingfilledin function like yours. Everything works except the setfocus order. For example in my form there is "E-mail" and "PhoneNumber" labels. "E-mail" positioned to number 11 and "PhoneNumber" positioned to 12. But ctl is reading PhoneNumber before E-mail. Then my Setfocus is setting the focus to PhoneNumber which is wrong. Any way to give a reading order to ctl? Thanks in advance.

[@abo3621]

@@WiseOwlTutorials Thank you again! Btw for whom looking for the solution, since I have more than one frame I have used if statements to find tabindex for different frames. As you already know for every frame, tabindex is starting from 0, therefore, there could be same tabindexes for different forms.

[@narutofan9127]

Is there a way to also loop the AfterUpdate for several textboxes like you did with the EverythingFilledIn function? Also what if I have optional textboxes, can you still use a loop and somehow not apply the input validation for them? Thanks, these tutorials has helped alot for my summerjob

[@WiseOwlTutorials]

Hi! I'm not sure exactly what you mean by "loop the AfterUpdate", what do you want to happen? For optional textboxes you just need some way to indicate that they're optional - you could add a prefix or suffix to the name of the control, or add some text to the Tag property of the control. You can then test for this text using an If statement and apply the validation on a conditional basis. I hope that helps!

[@camilo11315]

In Minute 34:02 why we have three "IF" statement but only two "End IF" at the end of the code. I thought that every time you started an If statement you needed to close it with an "End IF". Thank you for anyone's feedback.

[@scotolivera8207]

one line if statement can be used without End if, watch the previous video on using IF statement.

[@shishupalchoudhary2918]

Great video. Very useful.

[@scotolivera8207]

with all respect sir, I think keypress event will help as validating ctrl

[@WiseOwlTutorials]

Thanks George, it's a good suggestion but be careful because your validation code will be triggered each time you press a key which might slow things down!

[@scotolivera8207]

WiseOwlTutorials you are right sir.

[@giangpham1946]

Thank you so much for a great tutorial. I also have 1 question, can we create a function to make every textbox and label back to normal when the right data was inputted in instead of using afterupdate event for each textbox?

[@giangpham1946]

I mean that: Is there a way to make a function like EverythingFilledIn to change all the textboxes back to white and label to the old color instead of doing event for each textbox? My purpose is to take all textboxes back to normal instead of using event for each textbox because if we have 20 textboxes, doing mannually for each textbox is really time-consuming.

[@giangpham1946]

Thank you! Simple and nice solution.

[@chahineatallah2636]

Hello guys, wanted to ask you what is the difference between hide and unload , if I want to remove the form after I filled in which one is better to use or should be use?

[@WiseOwlTutorials]

Hi! Part 3 of the Excel VBA Forms playlist explains the difference between Load/Unload and Show/Hide. I hope it helps!

[@michaelhuis]

Hello WiseOwl, If have a question about the "private function Everything filed in" When you have for example 20 texboxes you still have to write a code for eacht texbox isnt it? Only you seperated it from the main private sub. Iam a noob but i learn realy quick from your toturials! Keep up going the good work!

[@michaelhuis]

Thank you very much.. I need to be more patient ;-) But i have another question: In the totorial you change all the label names at the same time. I understand it completly, but... My labels are named: lbl* My textboxes are named: txt* So the rule: Controls(ctl.name & "label).Forecolor = rgbRed is not working for me. I tried: Controls("lbl" & ctl.Mid(ctl, 4, 99)).ForeColor = rgbRed Example: txtGross > after the mid(ctl, 4, 99) function it will be > Gross (the mid function from the normal Excel worksheet > =MID(*, *, *)) Together with lbl > lblGross I know i do it wrong. But is there a solution for me? I tried many other ways to but it didnt work. Thank you very much!

[@thorjarlberglund9842]

I have a little trouble with the speed of your writing. Have to pause alot :D But Im totally noob (and norwegian) But I love the way the videos are made so its actually possible to pause them with a single click. And by the way! I love the accent! :)

[@satyenshukla3823]

what option available for lost focus as it is not in vba excel

[@WiseOwlTutorials]

Hi, you can use the Exit event learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/enter-exit-events I hope it helps!

[@armandoaldaz9210]

There is a problem when i type the Controls(ctl.Name & "Label"). ForeColor=rgbRed, when i run the userform, VBA stops and the Debug send me the message " Run-time error '-2147023809(80070057)': i see the code as same as yours...dont know what to do...

[@armandoaldaz9210]

Thanks for your quick response, it really helps, that's exactly the mistake i made, now i can watch your next tutorial. Also i want to thank you for all these tutorials... You dont know how much you help us to improve at our daily activities. Kind regards.

[@buhaytza2005]

19:02 "Bye-bye hard work" :)

[@scotolivera8207]

haha

[@unisoft5956]

Wallah