Reputation: 103
I have created a code which will loop through all the sheets in a workbook and list down all the charts by the chart name along with the sheet name This code is working fine.
Now I want to create a hyperlink of the chart name so that when I click on it the it will direct me to the chart where it is exactly placed.
The below mentioned code is creating a hyperlink but when I am clicking on it then its giving a message "Reference not valid"
I guess the below mentioned line needs to be modified:
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & .ChartObjects(lngC).name & "'" & "!A1", TextToDisplay:=.ChartObjects(lngC).name
Please suggest.
Overall Code:
Sub Chartlist()
Dim wks As Worksheet
Dim lngS As Long, lngC As Long, lngX As Long
Set wks = ActiveWorkbook.Worksheets.Add
For lngS = 1 To ActiveWorkbook.Sheets.count
With ActiveWorkbook.Sheets(lngS)
For lngC = 1 To .ChartObjects.count
lngX = lngX + 1
wks.Cells(lngX, 1).Value = .name
wks.Cells(lngX, 2).Value = .ChartObjects(lngC).name
wks.Cells(lngX, 2).Activate
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & .ChartObjects(lngC).name & "'" & "!A1", TextToDisplay:=.ChartObjects(lngC).name
Next lngC
End With
Next lngS
wks.Columns(1).WrapText = False
wks.Columns(1).EntireColumn.AutoFit
Set wks = Nothing
End Sub
Upvotes: 1
Views: 1731
Reputation: 11712
Instead of
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & .ChartObjects(lngC).Name & "'" & "!A1", TextToDisplay:=.ChartObjects(lngC).Name
use
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & .ChartObjects(lngC).Parent.Name & "'" & "!" & Range(.ChartObjects(lngC).TopLeftCell, .ChartObjects(lngC).BottomRightCell).Address(0, 0), _
TextToDisplay:=.ChartObjects(lngC).Name
where,
.ChartObjects(lngC).Parent.Name
is sheet name where chart exists and
Range(.ChartObjects(lngC).TopLeftCell, .ChartObjects(lngC).BottomRightCell).Address(0, 0)
is address of chart.
Upvotes: 1