Combo Box vba that works like Forms Menu combo box

[Hector Fernandez]

I've looked for quite some time for a solution, but can't seem to find
it.

All I want to do is make a combo box in an User Form that works just
like the combo box in the Forms toolbar.

I can get the User Form combo box to show a range of options from which
to choose from (same as the Input Range Control for the combo box in
Forms, but I can't for the life of me figure out how to get it to
simply put in a number in a linked cell (as the Forms toolbox does).

With forms, the selection you make puts a number, depending on the
combo box choice, in the Linked Cell of my choice. I want to do
exactly the same thing with the User Form combo box - HELP

 

[thesquirrel]

You can use code like this to place the value of the combo box in a
cell.

Private Sub ComboBox1_Change()
Range("A1").Value = ComboBox1.Value
End Sub

that is very basic code that will enter the value in a cell whenever
the combobox changes.

Hope that helps.

theSquirrel

 

[Hector Fernandez]

Squirrel,

I've tried your recommendation, however all it does it put the actual
text from the ComboBox into the chosen linked cell.

So, if my list of options in the ComboBox is, for example:

Car
Boat
Plane

If I choose Boat I would expect it to put a value of 2 in my chosen
linked cell, however it is putting the word Boat in the cell.

What am I doing wrong?

 

[thesquirrel]

Nothing at all, it was my mistake, I thought you wanted the chosen
value of the combo box to be in the cell.

Change the code to the following:

Private Sub ComboBox1_Change()
Range("A1").Value = ComboBox1.ListIndex + 1
End Sub

This will place the number in the cell instead of the value.

Just for your information, the index for list boxes starts at 0 instead
of 1, thus the + 1 at the end.

theSquirrel

 

[Hector Fernandez]

thesquirrel,

Thank you, exactly what I wanted.

I knew it had to be something simple, I just couldn't find out what -
I'm a noob at this.

Again, thanks.