How To Validate Userform Inputs With Excel VBA (Excel Userforms For Intermediates 2)

preview_player
Показать описание
How can you validate text box entries in a userform with Excel VBA? In this video, I show you the actual technique I use on real-world Excel VBA projects to get this done.

FREE Download – Your Excel Cheatsheet LINK 👇👇👇

Viewers of the channel might know that I have mixed feelings about Excel userforms. Yes, I love the visual effect, interactivity and (if the form is set up properly) speed of data input. They can be a real ‘wow’ factor in an Excel spreadsheet.

At the same time, I have become increasingly aware of practical issues with userforms. They are code-intensive. In recent times, I have had more and more display issues with userforms. Trying to get them to display in the same place and to size in the same way on different systems has been problematic. It’s got me in trouble on client projects a couple of times!

Download File LINK👇👇👇

I have grown to appreciate the value of creating a data input ‘form’ in a worksheet, rather than a userform. With some attention to formatting, it can look almost as good AND there are no display issues. Moreover, we can harness cell validation to make sure data is in the correct format. It’s probably my go-to approach these days.

Which brings me to the topic of this video. There are few, if any, validation controls in Excel userforms. So, how can we guarantee that boxes are completed and that the right data is entered? We don’t want somebody entering ‘twenty’, for example, when asked for an age, when formulae in the spreadsheet depend on a numerical input (ie. 20.) Yes, this has happened to me!

The only way is to create a ‘custom’ validation mechanism in Excel VBA. In this video, I talk you through the process. First, I use the worksheet (yes, the worksheet!) to specify the controls I want to include in the validation check. Then, I set up VBA code to loop through the worksheet entries, locate the corresponding controls (ie. textboxes) in the userform, and check if an entry is present using a conditional statement. Finally, I integrate this validation routine into the main code so that it is the first thing that runs when the button is pressed.

It’s a lot of code for something that can easily be done with worksheet cells. But, I have found this approach to be a robust and flexible one for validating userform inputs with Excel VBA.

Give it a try and let me know what you think.

FREE Download – Your Excel Cheatsheet LINK 👇👇👇
Рекомендации по теме
Комментарии
Автор

Welcome back to the Excel Userforms for Intermediate series. Leave me your questions below 👇 I will get back to you

TigerSpreadsheetSolutions
Автор

VERY HELPFUL! BEEN SEARCHING FOR A SOLUTION ON MY PROJECT FOR ALMOST A WEEK NOW THEN I FOUND THIS! THANK YOU SO MUCH! MORE POWER!

markanthonydizon
Автор

A great tip to write the names of the textboxes, comboboxes etc. in a worksheet and use a For Each Next loop through the range. A huge time saver and much much clearer code in my userforms to work with. Thank you!!!

JanBolhuis
Автор

Thank a lot for these tutorials! Will be looking forward for the next videos 😆

febrianykusumastuti
Автор

Many thanks, I have learned a lot from your videos. But I am having trouble writing for a code to find the last duplicate value from the user form to update. I tried the lookup(2, 1/ but seems to update all the duplicate values. Have you a video on this?Many thanks again

paulcorker
Автор

Nice tutorial... next steps would be to alter the textbox's to highlight them if they are missing or have incorrect inputs.

CybrPp
Автор

Another great tutorial. Your Video's are helping a beginner in VBA understand so much. After following your tutorials on creating UF, there is a tutorial on editing data from in the data base by searching for a name, but could you do a tutorial on searching for data with multiple criteria in the same format as the edit tutorial with the out put using UF?

adamrussell
Автор

this tutorial really helps me. Thank you!

bellalee
Автор

Buenas estimado, tengo un problema con una macro quiero registrar a través de una userform de cuadro combinado, pero lo qué pasa es que a veces deja de responder y no me registra, sabe por qué podría pasar eso

renzoarrasco
Автор

Great videos! thanks so much - they've really helped me create a good user form. I would like to know how to validate a date field please. I have a Date of Birth field on my form, I'd like to a)ensure it is a date format and b) preferably in the following format dd/mm/yyyy. If this is covered in any of your videos, please can you point me in the right direction. Thanks so much!

julieannedouglas
Автор

Hello, I was wondering if you would be able to help me with an issue with validation. I have managed to validate previous textboxes and combo boxes using this. If Me.textbox1.Value = “” Then Msgbox “please enter date” ext. Now for the next piece of validation I am stuck.

Say there is data in textbox 4, I then need either textbox 5 or 6 to have data in, but not both. I will also not need data in textboxes 7, 8 or 9. Then if text box 7 has data, I then need either textbox 8 or 9 to have date, but not both. If 7 has data then I do not need any in 4, 5 or 6.

Hope you can help

matj
Автор

What should I do if I get a runtime error when it is not exiting the If statement in the check sub?

hhhgghhghggj
Автор

Very usefull again. Thanks.
Is there allready a Video by you, where you explain how to convert numeric output from a textbox to an excel worksheet, which is entered as text and needs to become a number again in the right form. So it can be used for further analyses.
I got the conversion but textboxvalue 0.01, 0.1 or 1 all give the same result 1. But I like to learn and understand why this is happening and how to solve this.
Could you help me out, please?
Thanks allready for everything I’ve learned from your videos. Patrick

patrickaugustus
Автор

Have you ever written VBA code that pulls LOV values from a worksheet table? It would be great if your code could respond dynamically to updates in an LOV table and not have to be edited every time an LOV changes. It would empower content managers who were not VBA developers to update LOVs in tables and free VBA developers from having to do less interesting code maintenence work. Is this possible in VBA?

garyjoaquin