Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a combo box activate different macros | Excel Discussion (Misc queries) | |||
Dynamically Add Combo Boxes | Excel Programming | |||
dynamically assigning to a combo box | Excel Programming | |||
Create combo box dynamically | Excel Programming | |||
how to add a combo box in to a excel cell using VBA dynamically? | Excel Programming |