Hi Murray,

Typically the chart menu bar is only activated (visible) while a chart is
selected, in which case there's no problem. However user might have the
chart bar permanently visible. You could reset to the "typical" arrangement
though first you need to ensure a chart is not selected before changing the
visible property.

I think that is the simplest way to go. However you could install your own
set of WithEvents to flag when a chart is selected / deselected, or another
sheet activated - on which a chart may or may not already be selected.

Just an outline of what you might consider, normal module & two class's -

' code in normal module
Dim clsXL As cls_AppEvents

Sub auto_open()
Dim sht As Object
Set clsXL = New cls_AppEvents
Set clsXL.xl = Application
On Error Resume Next
Set sht = ActiveSheet
' no active sheet if this is in an installed addin
If Not sht Is Nothing Then
clsXL.xl_SheetActivate sht
End If
End Sub

Sub auto_close()
Set clsXL = Nothing

End Sub

' code in cls_AppEvents

Public WithEvents xl As Excel.Application
Dim colCharts As New Collection
Dim bChartSheet As Boolean

Public Sub xl_SheetActivate(ByVal Sh As Object)
Dim chObj As ChartObject
Dim clsCht As cls_ChtEvents
Set colCharts = Nothing
If TypeName(Sh) = "Chart" Then
Set clsCht = New cls_ChtEvents
Set clsCht.cht = Sh
colCharts.Add clsCht
If bChartSheet Then
' another chart sheet
Else
bChartSheet = True
MsgBox "Chart Sheet"

End If
ElseIf bChartSheet Then
'chart deactivate event should have fired
'MsgBox "Not a chart sheet"
bChartSheet = False
End If

For Each chObj In Sh.ChartObjects
Set clsCht = New cls_ChtEvents
Set clsCht.cht = chObj.Chart
colCharts.Add clsCht
Next
End Sub

' code in cls_ChtEvents

Public WithEvents cht As Excel.Chart

Private Sub cht_Activate()
MsgBox cht.Name & " activated"
End Sub

Private Sub cht_Deactivate()
MsgBox cht.Name & " de-activated"
End Sub

For your purposes, to eneable/disable your menu, set a global flag in the
normal module when a chart is selected/de-selected. Call a sub in the normal
module with onTime to compare the flag with your enabled state and change if
necessary. Ie, don't need to change settings if de-selection was due to
selecting another chart.

Regards,
Peter T

"Murray" wrote in message
oups.com...
Thanks Doug

That's a worthwhile option, but still doesn't really achieve what I was
after. Perhaps I've confused the issue by talking about the worksheet
menu bar - I suppose what I really want to know is how do I put a new
menu item on my menu that is only active if a chart is selected? This
would be preferable to coding a check to see if a chart is selected and
presenting the user with a message if one isn't. It's much nicer to
just make the option unavailable in the first place.

Regards

Murray