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.

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

Perfect...thanks.


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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