Hyperlink a chart? 2

[N1GHTEYES]

A colleague of mine often tends to have quite large sheets with various datasets scattered around it, some of which have associated charts. Maybe not good practise, but that's his problem. He finds it a bit of a pain nevigating quickly to any given chart, if it is somewhere off the displayed area.

He asked me if there was any easy way of listing and finding charts. I suggested using F5, objects, tab - which works fine, but it got me thinking. I'd like to be able to be able to add a new button to an existing add-in toolbar which would run a macro to create a list of all the charts on the active sheet and include hyperlinks to them.

So far, i've got the following code:

Sub CreateChartsIndex()
Dim i As Long, numcharts As Long
Dim Hanchor As Range, Haddress As String, Hsubaddress As String
Dim shtname As String
Dim h As Hyperlink
numcharts = ActiveSheet.ChartObjects.Count
startrow = ActiveCell.Row
startcol = ActiveCell.Column
ActiveCell = "Chart Title"
For i = 1 To numcharts
    Cells(startrow + i, startcol) = ActiveSheet.ChartObjects(i).Chart.ChartTitle.Text
    shtname = ActiveSheet.ChartObjects(i).Parent.Name
    Hsubaddress = Replace(ActiveSheet.ChartObjects(i).TopLeftCell.Address, "$", "")
    If Cells(startrow + i, startcol).Hyperlinks.Count > 0 Then
        For Each h In Cells(startrow + i, startcol).Hyperlinks
            h.Delete
        Next h
    End If
    Set Hanchor = Cells(startrow + i, startcol)
    Haddress = shtname
    Hsubaddress = Haddress & "!" & Hsubaddress
    ActiveSheet.Hyperlinks.Add anchor:=Hanchor, Address:="", subaddress:=Hsubaddress
Next i
End Sub

This creates the list of charts, and hyperlinks to the cell at the top-left of the chart. This is almost what I was after, and I suppose will do, but does anyone know how I could make the chart itself the target of the hyperlink? I've tried using the chart name in the subaddress instead of the cell reference, but that does not work.

Tony

 

[SkipVought]

Rnages only..

'places chart huperlinks on Sheet3 starting in Row 1

    Dim ws As Worksheet, co As ChartObject, lRow As Long
    
    lRow = 1
    For Each ws In Worksheets
        For Each co In ws.ChartObjects
            ActiveSheet.Hyperlinks.Add _
                Anchor:=Sheet3.Cells(lRow, 1), _
                Address:="", _
                SubAddress:=co.TopLeftCell.Parent.Parent.Name & "!" & co.TopLeftCell.Address, _
                TextToDisplay:=co.Name
            lRow = lRow + 1
        Next
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Skip, does that not also just link to the address rather than the chart itself? It is a bit neater than mine (your code does tend to be), but how does this do any more than the code I already posted? It is morning here and that is never my best time, so forgive me if I'm missing the obvious.

The reason I want to link to the chart object itself (as the target of the hyperlink) is so that, if the user subsequently moves any of the charts, the table of hyperlinks will still correctly point to them, whereas otherwise it will simply point to where they used to be when the table was created.

Tony

 

[SkipVought]

You can run the procedure any time to renew the HLs

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

I agree. I was just trying to figure out if there was a way whereby I didn't have to...

However, this started out as an offhand query and I've already given the guy two solutions, so I guess that should be good enough.

Tony

 

[SkipVought]

This really is not a hyperlinks solution, but it does select the chart.

First run this to list the charts.

Sub HLinkCharts()
    Dim ws As Worksheet, co As ChartObject, lRow As Long
    
    lRow = 2
    For Each ws In Worksheets
        For Each co In ws.ChartObjects
            Sheet3.Cells(lRow, "A").Value = co.Parent.Name & "!" & co.Name
            lRow = lRow + 1
        Next
    Next
End Sub

Then this for the 'hyperlink' simulation logic

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim a, co As ChartObject
    
    If Target.Row = 1 Then Exit Sub
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        a = Split(Target.Value, "!")
        Sheets(a(0)).Activate
        
        For Each co In ActiveSheet.ChartObjects
            If co.Name = a(1) Then co.Select
        Next
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

What about creation of a dummy hyperlink and FollowHyperlink event?

Sub CreateChartsIndex2()
Dim i As Long, numcharts As Long
Dim HScreenTip As String
Dim HRange As Range, oChart As ChartObject
numcharts = ActiveSheet.ChartObjects.Count
startrow = ActiveCell.Row
startcol = ActiveCell.Column
ActiveCell = "Chart Title"
For i = 1 To numcharts
    Set HRange = Cells(startrow + i, startcol)
    Set oChart = ActiveSheet.ChartObjects(i)
    With HRange
        .Hyperlinks.Delete
        .Value = oChart.Chart.ChartTitle.Text
        HRange.Hyperlinks.Add anchor:=HRange, Address:="", SubAddress:=HRange.Address, ScreenTip:=oChart.Name
    End With
Next i
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Me.ChartObjects(Target.ScreenTip).TopLeftCell.Select
End Sub

combo

 

[N1GHTEYES]

Thanks guys.

Skip: nice neat minimal solution - as usual.
Combo: extremely cute - I particularly like the self-referential "dummy" hyperlink and the use of the screentip to store data. BTW, In the second sub, shouldn't "Me.ChartObjects(Target.ScreenTip).TopLeftCell.Select", simply be "Me.ChartObjects(Target.ScreenTip).Select" if I want to select the chart itself rather than the cell where it lies?


If I was going to be picky (which I would not even dream of being since you have both been so helpful, as usual) I'd point out that neither solution quite meets the initial idea of being some code I could put behind an add-in toolbar button, because both of them require some extra code to be written for the calling workbook. Of course, in the code which created the list, I could refer to the workbook's VBA project and add the additional code. But that sounds a tad iffy - especially if it already has a selectionchange event or existing hyperlinks.

An alternative I was mulling over was to create labels which would go into the cells of the "chart list" (instead of merely writing each chart's name in the cells of the list) and to which I could assign code without worrying about overwriting existing code. The code for each label would, of course activate it's associated chart.

However, as I said, both are nice solutions and much appreciated - stars all round.

Tony

 

[combo]

Thanks for the star. If you need to select chart, then of course it can be done in the FollowHyperlink event.
It can be useful to apply Skip's idea to navigate inside workbook using single charts list, in this case ScreenTip:=co.Parent.Name & "!" & co.Name
and
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
a = Split(Target.ScreenTip, "!")
Sheets(a(0)).Activate
ChartObjects(a(1)).Select
End Sub

combo