PDA

View Full Version : Select and activate ComboBox entry by macro code



Snoopy22
02-02-2012, 04:58 AM
Hi All,

I am working in a spreadsheet (not a User form!) in Excel 2003.

What I would like to do is select and activate an entry of a Combobox by a VBA macro. I need this to run a large model in different scenarios, i.e. select Combobox entry 1 Base Case, calculate, copy data from one sheet and paste hadplugged values in another sheet, select Combobox entry 2 Best Case, calculate, copy data from one sheet and paste hadplugged values in another sheet, select Combobox entry 3 Worst Case, calculate, copy data from one sheet and paste hadplugged values in another sheet.

Everything works fine by using the macro recorder except the selection of the combobox item...

Thank you for your help in advance!

Kenneth Hobs
02-02-2012, 06:13 AM
I am not sure what you mean. For the ComboBox Click event, the ComboBox1.Value will be the one that is used in your code to do your thing.

If you mean run similar code in another macro, it is best to set your Click event code to be modular. This means that you send the value of the combobox to a routine and you would do the same for your other routine.

You can post your code between VBA code tags or attach a file to show more detail. Some limits on forum use apply until your 5th post.

Snoopy22
02-02-2012, 06:53 AM
Thank you for your quick reply.

It is just a very easy step and I don't know the code for it:

Imagine you click on a Combobox and select the first entry. That's all. The macro recorder doesn't recognise this initial step in my record and without it the whole macro will not work....

Kenneth Hobs
02-02-2012, 09:09 AM
Sheet1.ComboBox1.DropDown