In this screencast I am going to be showing you how to use combo boxes in Microsoft VBA
along with Microsoft Excel.
So what we're going to be creating in this screencast is the following.
So I've got the seven dwarfs here and I've got their hypothetical favorite colors and
their hypothetical heights, and we want to make a user form that we can press "Go" and
it will automatically bring up this user form that has combo boxes where the user can select,
for example bashful, and the height of bashful, and we see that is 29.5, and we can click
go and it automatically looks that up for us.
Now this isn't that useful when you have a small amount of data like this, but if we
had 10 to 20 columns of data and hundreds of different items this is a really nice way
to look up information.
So I am starting with just my basic data that I've put into an Excel spreadsheet.
I can do Alt+F11 to bring up the editor.
I've just inserted a module, and I've inserted a user form.
You can do that by up here, so I've already inserted a user form and I've inserted a module.
For the user form I've already formatted it, I've added a name here, the dwarf data base
down here in the properties window.
I've also added two combo boxes, I've added a quit button, and I am going to name that
quit button.
I am going to, I've already named that go button.
So now that I've made my framework for this let's put in some code.
The first thing I like to code is the quit button.
So this is what happens when that quit button is pressed.
The only thing that we want to do is unload NameForm.
NameForm is the name of my user form.
If you click on the user form this is the name of that form.
So when we run this user form we want to populate both these combo boxes, so this combo box
is going to be the dwarfs name, so that would be these seven items, and the second combo
box is going to be do we want the favorite color of that dwarf or the height of that
dwarf.
The best way to do this is to populate the combo boxes before you open up that user form.
So what I am going to do is I inserted a new module and we're going to put a sub in here.
So this is the sub that we're going to start with.
I am going to call this sub PopulateComboBoxes.
We are going to then add these into combo box 1 and these two items into combo box 2.
So to start out with I am going to make this sort of a general approach so it's not just
going to be for this seven, but I am going to make it so we could have more if you wanted
to, so this is going to be made pretty versatile.
What I am going to first do is select cell A2 in my code.
So I am going to say range A2 select.
I am also going to have some dimmed statements up here.
The approach is to select cell A2, and then I am going to record a macro here, because I forget
how to do Ctrl+Shift+Down, what I want to do is be able to Ctrl+Shift+Down to make a selection.
So I am going to start with A2, and I am quickly going to record a macro.
I am going to do Ctrl+Shift+Down, stop recording.
Back over to the editor it put that code into module 2, so I am just going to take that
code and cut.
I am going to delete this, remove module 2 without saving, and then I am going to paste
that, so that is how it would do a Ctrl+Shift+Down to select then these seven dwarfs.
That's a selection now, so I am going to add in a counter variable.
So I added in n rows as an integer.
We're counting the number of rows of our selection, so I did n rows equal selection.rows.count,
so now we're going to iterate through those seven items, or those nrows items.
So I am going to dim i as an integer, this is going to be an index of iteration for i=1
to n rows.
I am going to add in my next i so I don't forget, and I am going to do name NameForm.ComboBox1,
that's our first combo box, AddItem, and it's the first thing that pops up after putting
in ComboBox1.
If we're going to add an item that's going to be equal to the active cell offset i-1,0.
When i is 1 I am going to be in cell A2, and I want doc to be my first item, so I am going
to offset 0 columns and I am going to offset i-1 rows, so that would be zero.
For the second item I am offsetting from A2 one row, so that's why we get the i-1.
We're going to add all the dwarfs till the end, so this'll be from i=1 to 7 because we
have seven dwarfs, and this NameFormComboBox Additem is going to add each of those names
in this column, one at a time, to ComboBox1. and you notice we haven't even opened up the
NameForm yet, so this is just, we're populating the NameForm before showing it.
Next, we need to populate ComboBox2 with favorite color and height, so we're going to start
in cell B1.
OK again if I go back to my NameForm this is now what we're populating, we just populated
ComboBox1.
So back in my main module I am going to select cell B2 and do something similar as we did
above.
I selected range B1 here and I added two lines.
To add an item to ComboBox2 that's equal to whatever's in range B1, and then I also added
one for C1.
Another thing I am going to do is make a default list item.
If you don't do this then when you bring up the form, the drop boxes, they have an empty
value here which we don't want, we want there to be some sort of default or starting value.
So I've added two lines, the first one I am saying NameForm.ComboBox1.Text, so that's
going to be a text display, kind of a default value, it's just the active cell offset 0,0.
So at the time the active cell will be A2.
I've added a default text for ComboBox2.
The last thing we need to do is just show the NameForm, so NameForm.Show.
So let's see if this works up to this point.
So I am going to press F8 to go through this.
Well there is no equal sign here, if there's no equal sign you just add an item, so I am
going to step through this.
We count seven rows.
There's seven dwarfs, and I go through for each of those and we're adding an item to
ComboBox1, and then we have a default text string for ComboBox1, and then we add items
in B1 and C1, those are for the favorite color and the height for the dwarfs.
A default value for the ComboBox2, and then we show the form.
So far nothing happens when we do "Go".
So we would want, for example, if I had Sleepy and height we would want it to display 30,
so that's what we're going to do now.
When we select something from a drop down list it's assigned a list index.
The top of the list starts with the list index of 0, not 1, and that's a little counter intuitive.
So right now if I pressed go it's going to use 0 for the list index of ComboBox1, and
1 for the list index of ComboBox2, so we can use the returned list indexes for these two
combo boxes to return the value that we want.
The approach is going to be to select cell A2 and then we're going to offset the number
of rows that correspond to the index, so Doc, we would start in A2, and we're going to offset
0 rows because we want Doc.
If I wanted Sleepy, Sleepy would return a list index of 3, so again we're starting in
cell A2, but we're going to active cell offset three rows down.
So that's why these list indexes always start with 0 at the top because a lot of times they're
used in combination with active cell offsets.
For the column offset we're going to use the list index.
In this case we're going to have to start with cell B2.
So a 0 for the second ComboBox would keep this column, and a 1 would keep this column.
So the approach would be to select cell B2, and then we're going to active cell offset
the ComboBox1 list index for the number of rows that we're going to offset, and then
we're going to offset the column index by list index 2.
We can then return the value of what we're looking for.
So I am going to quit.
The code behind what happens now goes behind the "Go" button, so I am going to double click
on the "Go" button.
The first thing I would like to do is to get in the habit of selecting the sheet that I
want, so this sheet is named names.
Sometimes when you're working with multiple sheets if you don't specify the sheet you're
working with then It'll be doing all this on the wrong sheet, so that's just something
that's optional here, but I like to do.
So we're going to select that sheet and then I am going to select cell B2, select.
So that will select blue because that's going to be kind of our base then for all of this
output, and now we've just put in a single line that looks like this.
We're going to message box, remember our active cell is B2 because of this line.
We're going to message box ActiveCell.Offset.
NameForm.ComboBox1.ListIndex.This returns which dwarf we want.
The second one is for columns, the number of columns that we're going to offset.
So we're going to offset by the list index that we select in ComboBox2.
I am going to step through this, so I am going to open up my module 1, and I am just quickly
go through F8 to make sure this is all working, but we've already made sure this works.
We show the name form.
I am going to select Sleepy, favorite color, and we select "Go", and now we're stepping
through the code that's behind the "Go" button,and then we're going to select cell B2, and I
am going to message box ActiveCell.Offset.
Now our list index I can hover over list index for ComboBox1 is 3, so we're going to be offsetting
three rows, and we're going to be offsetting 0 columns, and that should give us yellow.
Is that right?
Sleepy's favorite color, make sure, is yellow, and then we end sub.
You notice that when that sub is ended we still have the user form open, so that's why
we can press the quit button here, and that unloads the name form and we finish off with
ending our original sub that's in our module that we ran everything from.
I am going to do one last thing, I am going to insert a button here.
We can assign that to our population combo boxes sub, I will just rename this real quick
to "GO" and we're all set.
So let's test this on a couple more cases, let's do Doc's height.
27, so not tremendously useful when we just have a limited amount of data here, but if
you had hundreds or thousands of data this is how you could easily search through it.
You could also adapt this so you could change things, so instead of just reading stuff you
could have input boxes or text fields here where you're changing different aspects of
your data.