Excel, macro, link to a graph sheet (Excel 2000 SR1)

[Sebastien59]

Hi,

I created one Excel VBmacro witch creates an index (sheet "Index&quot containing links to graphs (sheet "DISK-Server 1&quot in the same file :
With Sheets("Index&quot.Cells(NUMSERVER + 2, 2)
.Value = "Server 1"
.Hyperlinks.Add Anchor:=Sheets("Index&quot.Cells(NUMSERVER + 2, 2), Address:="\.!DISK-Server 1"
.Font.Bold = True
End With

Unfortunately, when I rename the file, links still point to old name..
How to create a relative link to one sheet containing one graph ?

Thanx !
Sebastien.

 

[RobBroekhuis]

Links pointing to a file won't update when the file is renamed. There is no mechanism for doing that.
Rob

 

[Sebastien59]

No mechanism ?
It is not a link pointing to a file, but a link pointing to a sheet of the same file.

So, how to create a relative link ?

 

[RobBroekhuis]

Ah. In that case, you don't want to set the address property, just the subaddress property. Unfortunately, I can't find a way to link to a chart sheet. If your chart is on a normal worksheet, you can do something like:

activecell.Hyperlinks.Add activecell,"", _
"sheet1!" & activesheet.chartobjects(1).topleftcell.address

which will take you to the cell at the top left corner of your chart object.
Rob