CODEWITHSUNDEEP
excelvbaeventscharts
Nat Aes
Nat Aes

Reputation: 927

Chart Events - Activate Worksheet

I use the following code that recognises when each segment of a chart is selected, and then links to a different worksheet:

Option Explicit

Public WithEvents CHT As Chart

Private Sub Workbook_Open()
Set CHT = ActiveSheet.ChartObjects(1).Chart
End Sub

Private Sub CHT_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
On Error GoTo Fin

If Range("A1") = "Product" Then
If Selection.Name = Range("J29").Value Then
    Application.Goto ActiveWorkbook.Sheets(Range("J29") & "P").Range("A1")
End If
If Selection.Name = Range("J30").Value Then
    Application.Goto ActiveWorkbook.Sheets(Range("J30") & "P").Range("A1")
End If
End If

If Range("A1") = "Country" Then
If Selection.Name = Range("J29").Value Then
    Application.Goto ActiveWorkbook.Sheets(Range("J29") & "C").Range("A1")
End If
If Selection.Name = Range("J30").Value Then
    Application.Goto ActiveWorkbook.Sheets(Range("J30") & "C").Range("A1")
End If
End If
Fin:
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If CHT Is Nothing Then
    Set CHT = ActiveSheet.ChartObjects(1).Chart
End If
End Sub

However, this only works when selecting any of the chart segments in the first sheet of the workbook and does not recognise the selection of any chart segments in other worksheets. How do I enable this to work for all worksheets? I thought the issue could be that the GoTo command doesn't activate the linked Worksheet, and hence attempted to add the following code:

If Selection.Name = Range("J29").Value Then
    Application.Goto ActiveWorkbook.Sheets(Range("J29") & "P").Range("A1")
    ActiveSheet.Activate
End If

Unfortunately this doesn't seem to work either. Any ideas?

Upvotes: 0

Views: 504

Answers (1)

Rory
Rory

Reputation: 34045

Try using the Workbook_SheetActivate event instead of the SheetSelectionChange event:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.ChartObjects.Count > 0 Then Set CHT = Sh.ChartObjects(1).Chart
End Sub

Upvotes: 1

Related Questions