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

Подписаться 94 тыс.

Просмотров 17 тыс.

50% 1

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 👇👇👇
www.tigersprea...
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👇👇👇
tinyurl.com/hy...
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 👇👇👇
www.tigersprea...

Опубликовано:

28 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 37

@TigerSpreadsheetSolutions 3 года назад

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

@attilaguba856 3 года назад

Hey ! I have just made your previous Userform tutorial and put some extras like date picker and now() function! Could be amazing how you can add a Delete button with functionality! 😊 many thanks

@Cyb3rP0p3 3 года назад

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

@TigerSpreadsheetSolutions 3 года назад

Absolutely DC - good video idea right there!

@febrianykusumastuti5339 3 года назад

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

@TigerSpreadsheetSolutions 3 года назад

Thank you and welcome to the channel - much more to come this year :-)

@adamrussell7631 3 года назад

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?

@TigerSpreadsheetSolutions 3 года назад

Adam - thank you and I have put your request on my list, good luck!

@paulcorker2069 3 года назад

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

@bellalee9703 3 года назад

this tutorial really helps me. Thank you!

@TigerSpreadsheetSolutions 3 года назад

You're welcome Bella - do check out our other videos

@robertobraicovick6867 3 года назад

Grazie! 🔝

@TigerSpreadsheetSolutions 3 года назад

Prego!

@matj2743 2 года назад

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

@TigerSpreadsheetSolutions 2 года назад

Hi Mat, this would be possible using conditional statements in your validation macro. We have plenty of videos about conditional statements in VBA - good luck!

@markanthonydizon50 2 года назад

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!

@TigerSpreadsheetSolutions 2 года назад

Great to hear my friend - do check out the others videos on the channel!

@hhhgghhghggj6997 2 года назад

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

@TigerSpreadsheetSolutions 2 года назад

Step through, tweak, test again! Good luck.

@JanBolhuis 8 месяцев назад

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!!!

@TigerSpreadsheetSolutions 8 месяцев назад

It's a pleasure - I use this idea in many aspects of VBA practice now. I'm sure you'll find it useful!

@JanBolhuis 8 месяцев назад

@@TigerSpreadsheetSolutions I have the following code to save data to a worksheet: Dim lastrow As Long Dim controlbox As Range lastrow = shtData.Cells(Rows.Count, 4).End(xlUp).Row + 1 For Each controlbox In Range("RangeData") Dim ColNmbr As Long ColNmbr = controlbox.Column shtData.Cells(lastrow, ColNmbr) = frmData.Controls(controlbox.Text) Next controlbox I'm trying to figure this out with option buttons. Because there is always a TRUE of FALSE, I wondered how you apply this to option buttons. Because at the end you want to write one value to your worksheet. PS: I'm using code names therefore shtData.

@garyjoaquin 3 года назад

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?

@TigerSpreadsheetSolutions 3 года назад

Gary - sounds interesting but I'm not familiar with LOV values, could you explain?

@garyjoaquin 3 года назад

@@TigerSpreadsheetSolutions, I apologize for not defining the term. "LOV" = "List Of Values" = the permitted values for a column, that for which we typically data validate using lists or ranges in Excel. Instead of entering the values as separate VBA lines of code defining pre-approved combo box / column values, is possible to: (1) Create an "LOV" worksheet containing one table for each column's pre-approved values; and (2) Write VBA code to read the contents of the worksheet table values and store them into a form's combo box object. I'm being very specific about storing list values in a table that can grow and still be identified by a table name, not a range whose cell coordinates would still need to be updated in the VBA code as new values were added to a worksheet range. You'd have to update the range cell coordinates in your VBA code as the range size grew. If you can pull a list of combo box values from a worksheet table then that code never needs to change. The table can grow as much as it needs to grow and its contents can always be referenced by table name. I used to work with an Oracle database administrator and developer who simplified the code behind his Oracle forms by storing all of his combo box values in a table. He never had to update his form's code when LOV's changed because the form code ran a query each time the form combo box was used, pulling the values from a table to populate the combo box list. It took a little bit more effort to write the code at first but it saved a lot of time in the long term. Users responsible for content management then used LOV management forms to update the list of values for each combo box. Can this kind of code be written in VBA for an Excel form? Does the VBA syntax exist to extract the values stored in an Excel table?

@julieannedouglas8028 3 года назад

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!

@TigerSpreadsheetSolutions 3 года назад

Hi Julie - that is a tricky one and, to be honest, I have never found an easy way. The way I use is to create text boxes for day, month and year, then I use some code in the background to validate the entries and convert it to a date for input to the spreadsheet. I have heard of people installing date pickers but I have never tried that myself. Good luck!

@julieannedouglas8028 3 года назад

@@TigerSpreadsheetSolutions Thanks so much for getting back to me. It would explain why i haven't had much luck finding a solution using Google. I'll try your approach - thank you!

@TigerSpreadsheetSolutions 3 года назад

@@julieannedouglas8028 Good luck and let me know how you get on

@renzoarrasco8424 3 года назад

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

@patrickaugustus6012 3 года назад

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

@TigerSpreadsheetSolutions 3 года назад

Hi Patrick - you're welcome! My first thought is to use the =Val function in VBA when transferring the data to the worksheet - I think you are already doing this. eg. Range("A1") = Val(Txt_Name) This should convert to a value. Then, try using a Double variable to hold the value, and hopefully retain the decimals. eg. Store_Variable = Val(Txt_Name) Range("A1") = Store_Variable Just an idea, let me know how you get on. It's something we may cover later in this series

@patrickaugustus6012 3 года назад

@@TigerSpreadsheetSolutions onto the job that is. I transfer data from userform to excel by using the code I learned from your userform beginner series. I’ll try the val function and will let you know the results! Is there a good book you recommend where vba functions are well explained? I also like the paperstuff and try to understand why things are happening. Is there another way to contact you even I have questions?

@TigerSpreadsheetSolutions 3 года назад

Patrick - I don't actually recommend a particular book, my learning style is more practical and based around real-world problems. In a book, you will learn a lot of things you may never use. At the same time, I know that style suits some people. Nevertheless, I would recommend always focusing your learning around jobs you are trying to get done. I'm happy to answer questions in the comments when I have time. To get access to me and a network of like-minded learners, check out our learning community on the website. Good luck.

@patrickaugustus6012 3 года назад

@@TigerSpreadsheetSolutions Thank you. Time for programming tegen 😁

@patrickaugustus6012 3 года назад

Hi Chris, I integrated the Val-function in combination with the decimal-value. It works perfect. The value from the TB is transfered to the excel-cell exactly as it is entered in the textbox. Great tip sensei 😁🙏🏻