Hello

I have created a new menu (as per the many excellent examples that
appear here) that will work via an add-in and adds a menu to both the
Worksheet and Chart Menu bars.

The menu has several items, but some are only applicable in a certain
context. For example (and specifically) one of the items is only
relevant if a chart is selected. Is it possible to have the menu item
disabled (greyed-out) on both the Worksheet menu bar and the Chart menu
bar, and only become active on the Chart menu bar when a chart is
selected?

eg:
MenuItem1 - always active
MenuItem2 - only active when a chart is selected
MenuItem3 - always active
etc...

I assume that I need to tap into an event somehow (like activating a
chart?) but don't know how to go about that. I'm still pretty much a
neophyte with this stuff.

Any suggestions would be very much appreciated.

Thanks and regards

Murray

Murray,

If it was me, I think I'd have two custom menus - one for the Worksheet Menu
and one for the Chart Menu. Then you could put the chart-context button
just on the Chart Menu toolbar.

hth,

Doug

"Murray" wrote in message
oups.com...
Hello

I have created a new menu (as per the many excellent examples that
appear here) that will work via an add-in and adds a menu to both the
Worksheet and Chart Menu bars.

The menu has several items, but some are only applicable in a certain
context. For example (and specifically) one of the items is only
relevant if a chart is selected. Is it possible to have the menu item
disabled (greyed-out) on both the Worksheet menu bar and the Chart menu
bar, and only become active on the Chart menu bar when a chart is
selected?

eg:
MenuItem1 - always active
MenuItem2 - only active when a chart is selected
MenuItem3 - always active
etc...

I assume that I need to tap into an event somehow (like activating a
chart?) but don't know how to go about that. I'm still pretty much a
neophyte with this stuff.

Any suggestions would be very much appreciated.

Thanks and regards

Murray

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

I agree. If you search on my name, you'll see that I was asking a similar
question last night. I did not get a workable answer. The idea I'd picked
up from an old post was to copy a built-in Excel button with the behavior
desired, i.e., only enabled while in a chart, and then assign your own
caption and OnAction. However, assigning the OnAction seems to eliminate
the original behavior (at least in XL 03).

Aside from that I'm not sure what you could use. If your charts are on
their own sheets, you could perhaps use SheetActivate/DeActivate events.
You might be able to do something with the SelectionChange event, e.g., if
no range is selected, but I'm not sure.

I still think my first answer is a good one because it's (fairly) simple.

good luck,

Doug

"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

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

PS -
Would only need to disable your menu button if a chart was deselected AND
the chart bar remains visible.
The events don't flag if a new chartobject has just been created.

Peter T

"Peter T" <peter_t@discussions wrote in message
...
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

It eliminates the behavior in all versions with commandbars.

--
Regards,
Tom Ogilvy


"Doug Glancy" wrote in message
...
I agree. If you search on my name, you'll see that I was asking a similar
question last night. I did not get a workable answer. The idea I'd
picked
up from an old post was to copy a built-in Excel button with the behavior
desired, i.e., only enabled while in a chart, and then assign your own
caption and OnAction. However, assigning the OnAction seems to eliminate
the original behavior (at least in XL 03).

Aside from that I'm not sure what you could use. If your charts are on
their own sheets, you could perhaps use SheetActivate/DeActivate events.
You might be able to do something with the SelectionChange event, e.g., if
no range is selected, but I'm not sure.

I still think my first answer is a good one because it's (fairly) simple.

good luck,

Doug

"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

Thank you Peter, Doug and Tom for your help.

Regards

Murray