Why can't I embed a button on a chart in Excel 2003?

[JB]

In Office Excel 2003 (11.6355.6360) SP1 I am trying to put a command button
and a couple of radio buttons on a chart. I cannot select any of the
controls on the Control Toobox as I can in Excel 2000.

 

[Andy Pope]

Hi,

You can not embed Active X controls within a chart and or chartsheet.
You can however use the controls from the Forms toolbox.

Cheers
Andy

 

[Ewan]

Thanks. I know that it can be done in Excel 2000 so I would assume that they
have removed this feature for a reason. Any ideas?

 

[Andy Pope]

No, you couldn't do it in xl2000 either. Check the controls I think you
will either find them to be from the Forms toolbox or not embedded
within the chart object if its a chart on a worksheet.

Cheers
Andy

 

[Tushar Mehta]

Well, one can always put the ActiveX control on the sheet, position it
so that it appears on top of the chart, and then group the two
together. However, in this case, you will need to access the chart
through a somewhat more complex method. The foll. works for a
commandbutton:

Option Explicit

Private Sub CommandButton1_Click()
With Me.Shapes(1).GroupItems(1).OLEFormat.Object.Chart
.HasTitle = True
.ChartTitle.Text = Format(Now(), "hh:mm:ss")
End With
End Sub

How did I figure out the heirarchy? By stepping through the object
model as below. Hint: start at the bottom and work your way up.

?typename(me.shapes(1).GroupItems(1).OLEFormat.Object.chart)
Chart
?typename(me.shapes(1).GroupItems(1).OLEFormat.Object)
ChartObject
?typename(me.shapes(1).GroupItems(1).OLEFormat)
OLEFormat
?typename(me.shapes(1).GroupItems(1))
Shape
?me.Shapes(1).GroupItems.Count
2
?me.shapes.count
1

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions