Context-sensitive custom menu item.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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