PDA

View Full Version : Solved: VBA Hyperlink Problem



maxhayden
05-27-2009, 05:00 AM
Hello there.

I have a peice of code that creates a new worksheet and names in Link1, 2 , 3... etc depending on the sheet order:

.Copy after:=Sheets(Sheets.Count)

ActiveSheet.Name = "Link " & Worksheets.Count - 2

I want to create a hyperlink to each new worksheet. I did this using the following code but the link doesn't work:



ActiveSheet.Cells(h, 5).Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Link" & Worksheets.Count - 2, TextToDisplay:="Click Here"

Can someone tell me what I'm doing wrong??

Ps. When I move my mouse over the "Click Here" link, ti comes up saying Link1 (so the naming must work). I think it is missing the !A1 cell reference at the end, but I'm not sure how to fit that in.

Thanks in advance for your help.

PS. And extra thought... It says Link1 on the hyperlink... when it should say Link 1... (missing the space!!) Hmmm...

maxhayden
05-27-2009, 05:07 AM
ITS OK!!! I SOLVED IT!!! HAHAHA!. It was the stupid space!! "Link " Rather than "Link"

!!!!

maxhayden
05-27-2009, 05:11 AM
Sorry! I thought I figured it out but was wrong... the space didn't work!!! Even though when I scroll the mouse over the link it comes up exactly the same as one that works!!! ?????:doh:

Hello there.

I have a peice of code that creates a new worksheet and names in Link1, 2 , 3... etc depending on the sheet order:








.Copy after:=Sheets(Sheets.Count) ActiveSheet.Name = "Link " & Worksheets.Count - 2








I want to create a hyperlink to each new worksheet. I did this using the following code but the link doesn't work:








ActiveSheet.Cells(h, 5).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "Link " & Worksheets.Count - 2, TextToDisplay:="Click Here"








Can someone tell me what I'm doing wrong??

Ps. When I move my mouse over the "Click Here" link, ti comes up saying Link1 (so the naming must work). I think it is missing the !A1 cell reference at the end, but I'm not sure how to fit that in.

Thanks in advance for your help.

PS. And extra thought... It says Link1 on the hyperlink... when it should say Link 1... (missing the space!!) Hmmm...

maxhayden
05-27-2009, 05:15 AM
Woops!!! That didn't work!! I got ahead of myself there! How bizzare! The link it creates is EXACTLY the same as one that I create manually - but the macro-made one doesn't work!

??? Hmmm

Bob Phillips
05-27-2009, 05:21 AM
Dim sh As Worksheet
Dim h As Long
h = 8
Set sh = ActiveSheet
With sh

.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Link " & Worksheets.Count - 2
.Cells(h, 5).Hyperlinks.Add Anchor:=.Cells(h, 5), Address:="", SubAddress:= _
"'Link " & Worksheets.Count - 2 & "'!A1", TextToDisplay:="Click Here"
End With


I have merged these threads.

maxhayden
05-27-2009, 05:26 AM
Thanks!! It was the sneaky ' before the link and ' before the !A1 that i missed out!

Thanks again for your help.

Bob Phillips
05-27-2009, 06:13 AM
Yes, you always need that with embedded spaces in a worksheet name.