Passing an event up the chain.

[atpgroups]

I am creating a specialised form or chart (an XY plot with the
underside of the curve forming a colour map of Z values, with
contours)
The chart is embedded in a class module which handles the extra
features (a whole lot of coloured polygons and lines added to the
chart's shapes collection)
When the axis limits are changed by the user, then the colour map
needs to be replotted. I can trap the chart events but none of them
seem to trigger when the user changes the axis limits. (unless one of
you knows better).
Plan B is an "Update Plot" context menu on the shapes collection. That
is actually pretty easy:

Private Sub ch_BeforeRightClick(Cancel As Boolean)
CommandBars("Shapes").Reset
With CommandBars("Shapes").Controls.Add
..Caption = "Update Plot"
..OnAction = ????????????????
End With
End Sub

However, getting the OnAction to call the correct subroutine in the
correct instance of the class is baffling me.
The event handler code is running in the correct instance, so I can
just update the plot on every right-click, but it is fairly time-
consuming so would prefer to do it only if the user selects the option
in the menu.

I think I want to set OnAction to be a sub in a standard module,
passed a link to the correct instance of the class module to run an
update method by the event handler. But I am getting in a terrible
muddle, and can't figure it out.

I suspect I might be trying to do the impossible, as you can only pass
a string in an OnAction parameter, and that probably isn't enough to
allow a standard module subroutine call a method of a specific
instance of a class.

Is it possible to iterate through all instances of a class looking for
one that contains ActiveChart? sort of like

For Each MyClass in <The current Scope>
if MyClass.EmbededChart.Name = ActiveChart.Name then
MyClass.UpDatePlot
Endif
Next

Perhaps I need to cancel the standard context menu, drop down my own,
and act on it internally to the instance of the class module?

 

[Andy Pope]

Hi,

I was able to get this to work, as there is no direct event.

ChartSheet code module.
'-------------------------------------------------------
Option Explicit
Private m_blnAxisSelected As Boolean

Private Sub Chart_BeforeRightClick(Cancel As Boolean)
If m_blnAxisSelected Then
Application.OnTime Now + TimeValue("00:00:02"), "ValidateScale"
End If
End Sub
Private Sub Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal
Arg2 As Long)

m_blnAxisSelected = False
If ElementID = XlChartItem.xlAxis Then
If Arg1 = xlPrimary Then
If Arg2 = xlValue Then
Debug.Print "Selected Primary Value Axis"
m_blnAxisSelected = True
End If
End If
End If

End Sub
'-------------------------------------------------------

Standard code module
'-------------------------------------------------------
Private m_dblScaleMin As Double
Private m_dblScaleMax As Double
Public Sub ValidateScale()

Dim blnFlagChange As Boolean

If TypeOf ActiveSheet Is Chart Then
With ActiveChart
With .Axes(2)
If .MaximumScale <> m_dblScaleMax Then
m_dblScaleMax = .MaximumScale
blnFlagChange = True
End If
If .MinimumScale <> m_dblScaleMin Then
m_dblScaleMin = .MinimumScale
blnFlagChange = True
End If
End With
End With

If blnFlagChange Then
MsgBox "Update Shapes", vbExclamation
Else
MsgBox "No change", vbExclamation
End If
End If

End Sub
'-------------------------------------------------------

It may work better if you do create your own Axis context menu. You can use
the Chart_Select event to determine whether the context menu should be
displayed or not.

Cheers
Andy

 

[andy the pugh]

I was able to get this to work, as there is no direct event.

Aha! Clever stuff. Thanks for that, I think I will use that instead of
the rather untidy Chart_MouseMove bodge I came up with.

However, thinking about this some more, I think I still need a context
menu for altering the Z scale (and a few other configurations). Could
you give any pointers on how to do that? Do I need to create a
msoPopup in the Application.Commandbars collection? That sounds rather
tricksy. Or can I create a Userform in a Popup style?

 

[Andy Pope]

Hi,

Have a read here.
http://www.mvps.org/dmcritchie/excel/rightclick.htm

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I was able to get this to work, as there is no direct event.

Aha! Clever stuff. Thanks for that, I think I will use that instead of
the rather untidy Chart_MouseMove bodge I came up with.

However, thinking about this some more, I think I still need a context
menu for altering the Z scale (and a few other configurations). Could
you give any pointers on how to do that? Do I need to create a
msoPopup in the Application.Commandbars collection? That sounds rather
tricksy. Or can I create a Userform in a Popup style?