Selecting/Activating Control Tools Combobox on Sheet

  • Thread starter Thread starter steve
  • Start date Start date
S

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...
 
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,

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

You always come through!

--
sb
Tom Ogilvy said:
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
Click to expand...
 
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
Tom Ogilvy said:
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
Click to expand...
 
"on" was a typo.

I guess I omitted the dim statement.

--
Regards,
Tom Ogilvy

steve said:
Tom,

Minor note:
Had to dim oleObj as OleObject (you knew that)
And had to change "on" to "in" (Excel insisted on that one)
Click to expand...
 
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
 
Back
Top