ActiveX ComboBox on Spreadsheet

**DM2** · 12-23-2009, 07:29 PM

How do I populate a ComboBox on a spreadsheet??

I have this table on the spreadsheet but would rather populate the box from the macro. I've tried the following, as well as a few others but it's not working.

Please Login or Register  to view this content.

**davesexcel** · 12-23-2009, 07:41 PM

Try this, it goes into the same module as your combobox because it is a worksheet activated code

Please Login or Register  to view this content.

**DM2** · 12-23-2009, 08:18 PM

Dave,
Thanks for the help!!!
Another question however...How do I set a variable based on what the user had selected?

**davesexcel** · 12-23-2009, 08:28 PM

All sorts of things can be done with it.

Please Login or Register  to view this content.

**DM2** · 12-23-2009, 11:42 PM

Thanks again Dave,
What i'm trying to do with the ComboBox, is use it in conjunction with an OptionButton used to set the spreadsheet to either SI units or US Standard units. If OptionButton1 is selected than its "SI units", if OptionButton2, than its "US Units"

When the user selects an item from the ComboBox, two variables are set based on SI Units or US Units.

- User selects the 1st item in the ComboBox.
- If OptionButton1 is set, Than Variable "SpecG" is set to...say...100 and "SpecT" is set to 200.
- If OptionButton 2 is set, than is't 300 and 400 respectively.

- User selects the 4th item in the ComboBox, that it's 104, 204, or 304, 404.

I'm going to use this variable in another calculation so i'm assuming the variable would need to be an "Integer"???

**memarti002** · 01-12-2010, 06:28 PM

Originally Posted by davesexcel

Try this, it goes into the same module as your combobox because it is a worksheet activated code

Please Login or Register  to view this content.

Just a note to anyone who is interested. I used

Please Login or Register  to view this content.

to define my Combo Box list and ran into an interesting problem. I was using the Combo Box to run a macro depending on what option I selected. The problem I ran into was that any time I clicked anywhere on the sheet, it re-ran the macro code for the option selected in the combo box. I tweaked it a little, to this:

Please Login or Register  to view this content.

and this seemed to fix the problem. It only triggered when I changed the selection in the combo box. If you don't have actions tied to the selections you probably won't see anything odd happening, but it may just make things in general run more smoothly with the tweak.