Hyperlinks in VBA

[Uwe]

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

--
Regards,
Tom Ogilvy

"Uwe" <[email protected]> wrote in message
news:[email protected]...
> 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

[Uwe]

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

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "Uwe" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>