Hyperlinks in VBA

[Guest]

Hi there,
I have a problem with Excel 2002 VBA. I'm trying to insert a Hyperlink in a
Cell through VBA Programming. Here's a part of the code:

MyRange1 = "D" & Myrow
MyRange2 = "A" & Myrow
Range(MyRange1).Select
MyArtNr = Selection.Value
Range(MyRange2).Select
MyABNr = Selection.Value
MyJump = Chr(39) & MyArtNr & "-" & MyABNr & Chr(39) & "!A1"
Cells(Myrow, Mycol).Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=MyJump

It does everything right but when I click on the cell I get a error
"Reference is no valid". When I do the same thing with the Makrorecorder it
works. The only difference is that I want to set the SubAdress with a
variable (and then loop)

I hope somebody can help.
Thanks

 

[Tom Ogilvy]

Your code worked fine for me. So I suspect that MyJump does not create a
valid address - perhaps a space is missing or you have an invalid character
or something.

My sheet was named She-et 2

in D1 I had She
in A1 I had et 2

MyJump = 'She-et 2'!A1

after running:
? worksheets("Sheet1").Range("J1").hyperlinks(1).SubAddress
'She-et 2'!A1


Sub AA()
Myrow = 1
myCol = 10
MyRange1 = "D" & Myrow
MyRange2 = "A" & Myrow
Range(MyRange1).Select
MyArtNr = Selection.Value
Range(MyRange2).Select
MyABNr = Selection.Value
MyJump = Chr(39) & MyArtNr & "-" & MyABNr & Chr(39) & "!A1"
Cells(Myrow, myCol).Select
ActiveSheet.Hyperlinks.Add _
Anchor:=Selection, Address:="", _
SubAddress:=MyJump
End Sub

 

[Guest]

Hi Tom,
Thank you for your help. The two cells contain numerical data and together
they represent the sheet name (with a hyphen). Maybe thats a problem.
I found another way that works: Since I loop through all sheets anyway I
grab the sheetname and use it instead of trying to piece it together.

MySheetName = ActiveSheet.Name
....
ActiveSheet.Hyperlinks.Add Anchor:=Cells(Myrow, Mycol), Address:="",
SubAddress:=Chr(39) & _
MySheetName & Chr(39) & "!A1"

After making sure the "Anchor" was set correctly (don't use "Selection") it
works like a charm.

Thank you again