Context-sensitive custom menu item.

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" <myall_blues@hotmail.com> wrote in message
news:1135297435.749733.126030@g49g2000cwa.googlegroups.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" <nobodyhere@replytogroup.com> wrote in message
news:%23ygmEq1BGHA.228@TK2MSFTNGP12.phx.gbl...
> 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" <myall_blues@hotmail.com> wrote in message
> news:1135297435.749733.126030@g49g2000cwa.googlegroups.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" <myall_blues@hotmail.com> wrote in message
news:1135297435.749733.126030@g49g2000cwa.googlegroups.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
news:O81OxA2BGHA.1008@TK2MSFTNGP12.phx.gbl...
> 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" <myall_blues@hotmail.com> wrote in message
> news:1135297435.749733.126030@g49g2000cwa.googlegroups.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