Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting/Activating Control Tools Combobox on Sheet
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... -- sb |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting/Activating Control Tools Combobox on Sheet
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 -- Regards, Tom Ogilvy "steve" wrote in message ... 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... -- sb |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting/Activating Control Tools Combobox on Sheet
Tom,
Works like a champ! Thanks Very Much! This will save me a lot of coding. You always come through! -- sb "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "steve" wrote in message ... 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... -- sb |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting/Activating Control Tools Combobox on Sheet
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" wrote in message ... 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 -- Regards, Tom Ogilvy "steve" wrote in message ... 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... -- sb |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting/Activating Control Tools Combobox on Sheet
"on" was a typo.
I guess I omitted the dim statement. -- Regards, Tom Ogilvy steve wrote in message ... 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" wrote in message ... 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 -- Regards, Tom Ogilvy "steve" wrote in message ... 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... -- sb |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting/Activating Control Tools Combobox on Sheet
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 -- sb "Tom Ogilvy" wrote in message ... "on" was a typo. I guess I omitted the dim statement. -- Regards, Tom Ogilvy steve wrote in message ... 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" wrote in message ... 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 -- Regards, Tom Ogilvy "steve" wrote in message ... 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... -- sb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
form control combobox | Excel Discussion (Misc queries) | |||
Control ToolBox ComboBox | Excel Discussion (Misc queries) | |||
Need to update a ComboBox when selecting a sheet | Excel Worksheet Functions | |||
Use of Control Tools | New Users to Excel | |||
Selecting or Activating a cell. | Excel Programming |