In yesterday's podcast, we were frustrated that the Validation dropdown would not allow us to type a few letters of the entry. In Episode 714, we will take a look at a tiny userform and macro to replace the validation dropdown.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Yesterday, we talked about setting up Data Validation and we're frustrated that we couldn't type the first letter of an entry in order to have Excel jump there And so, today I'm gonna try and use a user form we're going to create a quick user form here.
So in VBA, Insert, UserForm and we get a form about that size.
I'm gonna resize it because I just need room for basically a combo box.
Now, let's do a couple things here first of all we're gonna rename the form to be frmChoose and I'm going to change the caption to choose a value.
All right now, I want to add a couple of controls the first control is a combo box.
So, I'll draw a combo box here and then I need an OK button.
So, that'll be a command button draw the command button in.
Of course the caption on the command button is going to be OK.
Good enough, and we want that to be the default so that's true.
Okay now, for the combo box.
Let's go through here. There's something called RowSource and I already named that range.
So, it's sheet 1, exclamation answers.
We'll have our source there okay so, basically what we have is we just have a little user forum with a drop-down and an OK button.
When they press OK, though we need to have something happen.
So, I'm going to right click on the ok button and say view code and I'm just going to write the value that they select right into the active cell.
So, Activecell.value = me.combobox1.value and then I need to hide the form Unload Me.
Ok now, before the podcast started, I created a short little Macro here in a module.
It basically just says form choose, and I assign that to be control+L.
So, basically we choose any value hit control+L and the dialogue pops up same list as before, but the beautiful thing is if I start to type a value, it will jump right to that value and I can use the arrow keys to move through, I can even type a few letters.
So, for example, if I do Che it will jump to Cherries or Chi jump to Chips and basically now, I have an easy way where I can just hit control+L and very quickly start to type a value and get that in any cell.
A little bit harder to set up than validation certainly, but it seems a lot more flexible.
We could even come up with some additional code. That would say hey depending on for in column C, D or E we would provide a different list on the way in and always write that value back to the active cell.
I also like this because when we set up validation if you had 10,000 rows, we would have to copy the validation rule down ten thousand times in this case the control+L is just going to work in whatever the active cell happens to be hit the value choose the answer and you're good to go.
Well, I hope you found that helpful.
If you want to learn more about VBA, of course the book VBA and Macros for Microsoft Excel.
All of this code is in there how to create user forms and things like that.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Yesterday, we talked about setting up Data Validation and we're frustrated that we couldn't type the first letter of an entry in order to have Excel jump there And so, today I'm gonna try and use a user form we're going to create a quick user form here.
So in VBA, Insert, UserForm and we get a form about that size.
I'm gonna resize it because I just need room for basically a combo box.
Now, let's do a couple things here first of all we're gonna rename the form to be frmChoose and I'm going to change the caption to choose a value.
All right now, I want to add a couple of controls the first control is a combo box.
So, I'll draw a combo box here and then I need an OK button.
So, that'll be a command button draw the command button in.
Of course the caption on the command button is going to be OK.
Good enough, and we want that to be the default so that's true.
Okay now, for the combo box.
Let's go through here. There's something called RowSource and I already named that range.
So, it's sheet 1, exclamation answers.
We'll have our source there okay so, basically what we have is we just have a little user forum with a drop-down and an OK button.
When they press OK, though we need to have something happen.
So, I'm going to right click on the ok button and say view code and I'm just going to write the value that they select right into the active cell.
So, Activecell.value = me.combobox1.value and then I need to hide the form Unload Me.
Ok now, before the podcast started, I created a short little Macro here in a module.
It basically just says form choose, and I assign that to be control+L.
So, basically we choose any value hit control+L and the dialogue pops up same list as before, but the beautiful thing is if I start to type a value, it will jump right to that value and I can use the arrow keys to move through, I can even type a few letters.
So, for example, if I do Che it will jump to Cherries or Chi jump to Chips and basically now, I have an easy way where I can just hit control+L and very quickly start to type a value and get that in any cell.
A little bit harder to set up than validation certainly, but it seems a lot more flexible.
We could even come up with some additional code. That would say hey depending on for in column C, D or E we would provide a different list on the way in and always write that value back to the active cell.
I also like this because when we set up validation if you had 10,000 rows, we would have to copy the validation rule down ten thousand times in this case the control+L is just going to work in whatever the active cell happens to be hit the value choose the answer and you're good to go.
Well, I hope you found that helpful.
If you want to learn more about VBA, of course the book VBA and Macros for Microsoft Excel.
All of this code is in there how to create user forms and things like that.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
