Dynamically Activate a Combo Box
 

I know how to use VBA to activate a combo box by hard-coding the name (for
example, ComboBox1.Activate). But how do I activate a combo box when its
name can only be determined at run time?

On a worksheet, each of the first 100 cells in column B sits underneath a
combo box from the controls toolbar. The combo box names go down the column
like this:
Combo_B1
Combo_B2
Combo_B3...

So I can dynamically build the name of the appropriate combo box when the
cursor is located in the underlying cell:

Dim myComboName As String
myComboName = "Combo_" & ActiveCell.Address(0, 0)

What do I need to do now to activate this combo box? I think I might know
how to do it by looping through all of the controls on the sheet looking for
the one that matches the name I've constructed. But I'm hoping for more
direct and efficient method.

Thanks.

Dynamically Activate a Combo Box
 

ActiveSheet.OLEObjects(mycomboname).Activate



Greg Hoffman wrote:

I know how to use VBA to activate a combo box by hard-coding the name (for
example, ComboBox1.Activate). But how do I activate a combo box when its
name can only be determined at run time?

On a worksheet, each of the first 100 cells in column B sits underneath a
combo box from the controls toolbar. The combo box names go down the column
like this:
Combo_B1
Combo_B2
Combo_B3...

So I can dynamically build the name of the appropriate combo box when the
cursor is located in the underlying cell:

Dim myComboName As String
myComboName = "Combo_" & ActiveCell.Address(0, 0)

What do I need to do now to activate this combo box? I think I might know
how to do it by looping through all of the controls on the sheet looking for
the one that matches the name I've constructed. But I'm hoping for more
direct and efficient method.

Thanks.

--

Dave Peterson

Dynamically Activate a Combo Box
 

Perfect...thanks.


"Dave Peterson" wrote in message
...
ActiveSheet.OLEObjects(mycomboname).Activate



Greg Hoffman wrote:

I know how to use VBA to activate a combo box by hard-coding the name
(for
example, ComboBox1.Activate). But how do I activate a combo box when its
name can only be determined at run time?

On a worksheet, each of the first 100 cells in column B sits underneath a
combo box from the controls toolbar. The combo box names go down the
column
like this:
Combo_B1
Combo_B2
Combo_B3...

So I can dynamically build the name of the appropriate combo box when the
cursor is located in the underlying cell:

Dim myComboName As String
myComboName = "Combo_" & ActiveCell.Address(0, 0)

What do I need to do now to activate this combo box? I think I might
know
how to do it by looping through all of the controls on the sheet looking
for
the one that matches the name I've constructed. But I'm hoping for more
direct and efficient method.

Thanks.

--

Dave Peterson