Using VB to change Combo box selection

[Richard Clayton]

From within a VBA macro, I want to select an option from within a
dropdown list (combobox), so that once the macro has run, the combo box
is reset ready for the next use.

The combo box is populated from a static range within the same
worksheet.

For example:

Choose Name
Andrew
Brian
Charlotte
Diana

I want the Combo Box to be set by the macro to "Choose Name".

I have checked through the info on the forum and some is relevant but I
cannot get syntax etc quite right. I get error messages that say Object
not defined etc. (Still learning!). I have tried Dim cb As Object, and
various other futile forays into the unknown.

The Combo box appears to be named "DropDown2" - automatically.

Would be very grateful for a little help!

Thanks

Richard Clayton

 

[Harald Staff]

Hi Richard

That would be the combobox from the Forms toolbar. Try

Sheets(1).DropDowns(1).ListIndex = 1

Change sheet index and dropdown index to fit.

HTH. Best wishes Harald

 

[Geof Wyght]

Richard,
Well this is fresh on my mind. (See my post below.) Try:
ThisWorkbook.Worksheets("Sheet1").DropDown2.ListIndex = 1

By the way, in the Control Toolbox design mode, click
inside the Name Box (top left, to the left of the formula
bar), and you'll be able to change the name of the control
that you have clicked.
Geof.