SIMPLE Chart Events Question

[AHFoddeR]

Hey guys,

I've looked through this article http://www.computorcompanion.com/LPMArticle.asp?ID=221 but it's a bit above my head.

I've got basic knowledge of VBA and what I'm trying to do is quite simple so I've come here.

I want code to run depending on which series in a chart is selected.

E.g.

 If ActiveChart.SeriesCollection(3).Select Then
    MsgBox "You clicked Series 3"
    End If

I've got the code to run for each series, I'm just not sure how to tie it into a click event.

Any ideas?

Cheers,
Lucas

[ajryan88]

Hi,

Try this code if the chart is embedded on a worksheet (insert this code into the worksheet's module):

Public WithEvents cht As Chart

Private Sub cht_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    If ElementID = xlSeries Then
        MsgBox cht.SeriesCollection(Arg1).Name
    End If
End Sub

and insert this in the Worksheet_Activate event:

Set cht = Me.ChartObjects("Chart Name").Chart

If this code is on its own sheet, then use this code instead, on the chart's module:

Private Sub Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    If ElementID = xlSeries Then
        MsgBox Me.SeriesCollection(Arg1).Name
    End If
End Sub

Hopefully you can adapt this code to suit your needs.

If you have any trouble I will be happy to continue helping you with this

[AHFoddeR]

Hey AJ,

Where exactly is the Worksheet_activate event? This is what I have so far, all in the worksheet/tab code:

Public WithEvents cht As Chart

Private Sub cht_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    If ElementID = xlSeries Then
        MsgBox cht.SeriesCollection(Arg1).Name
    End If
End Sub

Private Sub Worksheet_Activate()
    Set cht = Me.ChartObjects("Chart 29").Chart
End Sub

[ajryan88]

The Worksheet_Activate event is accessible in the Worksheet module (right-click on the worksheet name in Spreadsheet view, and select "View Code" to bring up the Worksheet module). Insert your code from above into this module.

Hope this helps

[AHFoddeR]

Hey AJ,

Hmmm. I've copied all the code above into the Worksheet module... Is that correct? Or is the first part supposed to be somewhere else?

Nothing seems to be happening so far. Does it matter what type of chart it is? It's a stacked bar...

Cheers,
Lucas

[ajryan88]

Hi,

Can you please upload your workbook here so I can take a look at it? It sounds like you are doing everything in the right place...

Thanks

[AHFoddeR]

I just got an error. Not sure when or why it popped up but see the screenshot below...Attachment 260917

[AHFoddeR]

Hey AJ,

I can't upload the workbook since it's confidential info. So I replicated the process in a new workbook and of course it worked... Why wouldn't it be working in mine?

Questions:

• Do code in other worksheet modules affect this code?
• Do other tabs being locked have any impact?

Any other thoughts of what might be stopping the perfectly functioning code from working?

[AHFoddeR]

Hey AJ,

It's all working now. I think I accidentally reordered some of the code. I've got a couple follow up questions if you're keen. I sent you a private message. Let me know if you can help out

Cheers,
Lucas