CODEWITHSUNDEEP
excelhyperlinkvba
Stacey
Stacey

Reputation: 103

Hyperlink giving error "reference not valid" in a workbook

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

Answers (1)

Mrig
Mrig

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

Related Questions