Excel VBA embedded chart beforerightclick problems

Question

I would like to intercept right clicks over a text box inserted in the upper right corner of a chart. I have created VB code (see below) for Macro 3 and a Class module.

This code works but with problems that prevent me from achieving my goal.

1. The ElementID (IDNum in my code) returned is always xlnothing (or 28 as viewed in the debugger on the stop statement)

2.. The a,b, x,y values are all 0. Thus I don't know in the code where the cursor was right clicked.

3. I want to only respond to right clicks over the inserted text box and have a popup that provides extra information.

Can anyone provide input on what is happening and how to achieve my goal of responding to right clicks over the text box?

Thank you.

Module with sub to create chart and enable beforeright click

Dim myFinChart As New EmbChartCls

Sub Macro3()

'

' Macro3 Macro

'

    On Error Resume Next

    ActiveSheet.ChartObjects.Delete

    ActiveSheet.Cells.Clear

    On Error GoTo 0

    Columns("A:B").ColumnWidth = 10

    Range("A1").FormulaR1C1 = "Date"

    Range("B1").FormulaR1C1 = "Amount"

    Range("A2").FormulaR1C1 = "1/1/2011"

    Range("A2").AutoFill Destination:=Range("A2:A11"), Type:=xlFillDefault

    Range("B2").FormulaR1C1 = "10"

    Range("B3").FormulaR1C1 = "20"

    Range("B2:B3").AutoFill Destination:=Range("B2:B11"), Type:=xlFillDefault

    Range("B2:B11").NumberFormat = "$#,##0.00"

    Range("A1:B11").Select

    ActiveSheet.Shapes.AddChart(xlColumnClustered, Range("C1").Left + 10, 0, 600, 200).Select

    ActiveChart.SetSourceData Source:=ActiveSheet.Range("$A$1:$B$11")

    ActiveChart.Parent.Name = "ChAmt"

    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 450, 0, 118, 24).Name = "TBFC"

    ActiveChart.Shapes("TBFC").TextFrame2.TextRange.Characters.Text = "Final Column"

    ActiveSheet.Range("A1").Select

    Set myFinChart.myChartCls = ActiveSheet.ChartObjects("ChAmt").Chart

    End Sub

Class Module with name: EmbChartCls (The module must be named this or it won't work)

Public WithEvents myChartCls As Chart

Public Sub myChartCls_BeforeRightClick(cancel As Boolean)

Dim x As Long

Dim y As Long

Dim IDNum As Long

Dim a As Long

Dim b As Long

cancel = True

myChartCls.GetChartElement x, y, IDNum, a, b

Stop

End Sub

Answer 1

Just put the macro you want in a standard module as a Sub procedure with no arguments.

RIght-click the textbox > assign macro > select your macro.

No you can't cancel the right-click menu with _mousedown that has to be done in _beforerightclick.

Answer 2

Thanks for your excellent responses.

I can not find a way to cancel the standard popup with the _mousedown event.  Can it be done?

I would like to try the left-click by assigning a macro to the textbox. Could you provide some guidance on how to do it?

Answer 3

Good points.  I hadn't focused on the textbox issue.

GetChartElement won't know about the textbox.

And the chart's MouseDown event won't be triggered by clicking on the textbox

and the textbox doesn't have an event model

and you can't put an ActiveX label (which does have an event model) on a chart

I guess you are left with simply assigning a macro to the textbox.

It's a left-click rather than a right-click, but I don't see a way to do the right-click.

If you already have a macro assigned to it and need a different function you could test in the macro for the shift key being down, for example.

Answer 4

I did not understand GetChartElement.

I assume the Button parameter in the _MouseDown sub identifies the button?

And will the _mousedown sub recognize a mouse down event over the textbox that is in the charrt?

Is it possible to define an event to the textbox in the chart?

Thank you.

Answer 5

You have misunderstood GetChartElement.

x and y are values you provide and it returns data about the item at that position.

So you need

Private Sub myChartCls_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

  Dim IDNum As Long

  Dim a As Long

  Dim b As Long

  myChartCls.GetChartElement x, y, IDNum, a, b

  Debug.Print Button, Shift, x, y, IDNum, a, b

End Sub

If you want to do it on right-click then you would check for Button=2, and return Cancel from the BeforeRightClick event procedure.