Hyperlinks with variables

[Marty Lindower]

Hi all-

I'm trying to write some code to generate an appointment
book, with each worksheet being a different day, with a
master calendar page with clickable links that will take
the user to the clicked day's sheet. The sheets are named
with the date, ie, 10-14-03. The variable xSheet is the
name of the sheet, ie "10-14-03". I've tried using this
code:

Sub AddLink()
Sheets("Calendar").Activate
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
xAddress = xSheet & "!R1C1"
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:="", SubAddress:=xAddress, TextToDisplay:=xSheet
End Sub

It does generate a link, but when clicked, it comes up
with a Reference Not Valid error. Can someone help me
tweak this code so it works?

Thanks,
Marty

 

[Tom Ogilvy]

You never define xsheet.

This worked for me:

Sub AddLink()
xSheet = "Sheet3"
Sheets("Calendar").Activate
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
xAddress = xSheet & "!R1C1"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="", SubAddress:=xAddress, TextToDisplay:=xSheet
End Sub

Testing from the immediate window:
? Worksheets("Calendar").hyperlinks(1).SubAddress
Sheet3!R1C1


I am using A1 addressing and it still worked, but I would recommend using
the same addressing as your sheet.

 

[Marty Lindower]

-----Original Message-----
You never define xsheet.

Thanks for your reply, Tom. Your code does work, which is
interesting. Earlier in the code I use this to define
xSheet:

xSheet = xMonth & "-" & xDate & "-" & "0" & (xYear - 2000)

where xMonth, xDate and xYear are variables in a For..Next
loop. If I run the code and ?xsheet from Immediate, it
comes back as 11-1-03 (starting date is 11/1), but the
link doesn't work. If I replace the above line with:

xSheet = "zzz"

the link works fine. Maybe Excel doesn't like the way I
built the sheet name?? Any other suggestions?

Thanks again!

 

[Tom Ogilvy]

Sub AddLink()
xMonth = 11
xDate = 1
xYear = 2003
xSheet = xMonth & "-" & xDate & "-" & "0" & (xYear - 2000)
xSheet1 = "'" & xSheet & "'"

Sheets("Calendar").Activate
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
xAddress = xSheet1 & "!A1"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="", SubAddress:=xAddress, _
TextToDisplay:=Left(xSheet1, Len(xSheet1) - 1)
End Sub

Seems to work.

 

[Marty Lindower]

Thank you Tom - that solved it!

Marty