Selecting/Activating Control Tools Combobox on Sheet

[steve]

Working Excel97 SR2

I have a sheet with a number of comboboxes. When I enter a cell containing
a combobox I want to activate the combobox and do a
Combobox1.Dropdown

At present I am using event code for each cell but would like to write
generic code to identify the combobox in the cell. (I could use a secondary
column to contain the the combobox #)

Can anyone suggest code that could do something like
Comboboxx.Dropdown

Thanks in advance...

 

[Tom Ogilvy]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
for each oleObj on Activesheet.OleObjects
if oleObj.TopLeftCell.Address = Target.Address then
if typeof oleObj.Object is MSForms.Combobox then
oleObj.Object.DropDown
exit for
end if
end if
Next
End Sub

 

[steve]

Tom,

Works like a champ! Thanks Very Much! This will save me a lot of coding.

You always come through!

--
sb

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
for each oleObj on Activesheet.OleObjects
if oleObj.TopLeftCell.Address = Target.Address then
if typeof oleObj.Object is MSForms.Combobox then
oleObj.Object.DropDown
exit for
end if
end if
Next
End Sub

 

[steve]

Tom,

Minor note:
Had to dim oleObj as OleObject (you knew that)
And had to change "on" to "in" (Excel insisted on that one)

--
sb

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
for each oleObj on Activesheet.OleObjects
if oleObj.TopLeftCell.Address = Target.Address then
if typeof oleObj.Object is MSForms.Combobox then
oleObj.Object.DropDown
exit for
end if
end if
Next
End Sub

 

[Tom Ogilvy]

"on" was a typo.

I guess I omitted the dim statement.

--
Regards,
Tom Ogilvy

Tom,

Minor note:
Had to dim oleObj as OleObject (you knew that)
And had to change "on" to "in" (Excel insisted on that one)

 

[steve]

Tom,

You're more than forgiven... It works sooooooo good.
Having Option Explicit really helps catch this stuff...

Have you seen my follow-up question about a generic Combobox event?

What I want is to have the next cell selected after a combobox is changed.
ComboboxX_change
target.offset(1,0).select