Pull down menu (combo box) on chart sheet

Hello:

Would like to have a full-sized chart worksheet that has a pull-down
menu on it so I can select a different product to show while viewing
the chart.

I can do this fairly simply in a regular worksheet where the chart,
data, and the pull-down menu exist all on the same worksheet. However,
if I make the chart's location a new sheet, then I can't use the pull
down menu without flipping back to my data sheet.

Found a posting with the following code and it's almost what I need,
but want to be able to use a pull-down menu instead of the regular
list -- See Sub Tester1():

****************************************
Martin,
Here is one way you can attack this:

Sub Tester1()
Set lb = Charts(1).Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
With lb.ControlFormat
.ListFillRange = "Sheet1!A1:A10"
.MultiSelect = xlSimple
End With
End Sub

Sub tester2()
With Charts(1).Shapes(1).OLEFormat.Object
For i = LBound(.Selected) To UBound(.Selected)
Debug.Print i, .Selected(i), .List(i)
Next
End With
End Sub

HTH,
Tom Ogilvy
****************************************

Thanks!

Pablo -

Change xlListBox to xlDropDown in the second line of code.

You can do this easily enough manually. Get the chart and the dropdown
working on one sheet, copy the chart to another sheet, and copy the
control to the same sheet. You then need to reformat the control, making
sure that the sheet name is in front of the list fill range and linked
cell (i.e., Sheet1!$A$1:$A$10 instead of just $A$1:$A$10).

You can even change the chart to a chart sheet, and paste the control on
the chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Pablo wrote:

Hello:

Would like to have a full-sized chart worksheet that has a pull-down
menu on it so I can select a different product to show while viewing
the chart.

I can do this fairly simply in a regular worksheet where the chart,
data, and the pull-down menu exist all on the same worksheet. However,
if I make the chart's location a new sheet, then I can't use the pull
down menu without flipping back to my data sheet.

Found a posting with the following code and it's almost what I need,
but want to be able to use a pull-down menu instead of the regular
list -- See Sub Tester1():

****************************************
Martin,
Here is one way you can attack this:

Sub Tester1()
Set lb = Charts(1).Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
With lb.ControlFormat
.ListFillRange = "Sheet1!A1:A10"
.MultiSelect = xlSimple
End With
End Sub

Sub tester2()
With Charts(1).Shapes(1).OLEFormat.Object
For i = LBound(.Selected) To UBound(.Selected)
Debug.Print i, .Selected(i), .List(i)
Next
End With
End Sub

HTH,
Tom Ogilvy
****************************************

Thanks!