VBA-generated Hyperlink problem

[Notawahoo]

Thanks to this group (Brian Wilson) many years ago, I have some VBA
code which goes through a multi-tab workbook and creates a list of
hyperlinks on the first sheet which go directly to any of the tabs.
There are about 20 tabs, so you can't see them all at once, and the
hyperlinking is really useful.

My problem is that if a tab name has any special characters or spaces
in it, then the vba-generated hyperlink doesn't work. If I manually
go into "edit hyperlink" and click on the tab name, then it works
fine.

The hyperlink to tab DOCS works fine, and the one to tab ADM-SRC does
not work -- it says "reference is not valid".

I'm using Excel 2003.

Here's the vba code:

Sub AddHyperlinks()
Dim ScreenTipMsg As String
Dim WhereToGo As String

Range("A7").Activate
KeepGoing = "Y"

Do While KeepGoing = "Y"

iRow = Selection.Row
iStartCol = "A"
iEndCol = "B"

With Worksheets(1)
ScreenTipMsg = "Go To " & ActiveCell.Value & " Sheet"
WhereToGo = ActiveCell.Value & "!A1"
Range(Cells(iRow, iStartCol), Cells(iRow, iEndCol)).Select

.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=WhereToGo, ScreenTip:=ScreenTipMsg
End With

ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < " " Then
KeepGoing = "N"
End If

Loop

End Sub


Thanks in advance for any help you can offer!

Nancy

 

[Tom Ogilvy]

try changing

WhereToGo = ActiveCell.Value & "!A1"


to

WhereToGo = "'" & ActiveCell.Value & "'!A1"
Lightly tested, but this worked for me:

Sub AddHyperlinks()
Dim ScreenTipMsg As String
Dim WhereToGo As String

Range("A7").Activate
KeepGoing = "Y"

Do While KeepGoing = "Y"

iRow = Selection.Row
iStartCol = "A"
iEndCol = "B"

With Worksheets(1)

ScreenTipMsg = "Go To " & ActiveCell.Value & " Sheet"
If ActiveCell.Value Like "*[!A-Za-z0-9]*" Then
WhereToGo = "'" & ActiveCell.Value & "'!A1"
Else
WhereToGo = ActiveCell.Value & "!A1"
End If
Range(Cells(iRow, iStartCol), Cells(iRow, iEndCol)).Select

.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=WhereToGo, ScreenTip:=ScreenTipMsg
End With

ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < " " Then
KeepGoing = "N"
End If

Loop

End Sub

 

[Notawahoo]

try changing

WhereToGo = ActiveCell.Value & "!A1"

to

WhereToGo = "'" & ActiveCell.Value & "'!A1"
Lightly tested, but this worked for me:

Sub AddHyperlinks()
Dim ScreenTipMsg As String
Dim WhereToGo As String

Range("A7").Activate
KeepGoing = "Y"

Do While KeepGoing = "Y"

iRow = Selection.Row
iStartCol = "A"
iEndCol = "B"

With Worksheets(1)

ScreenTipMsg = "Go To " & ActiveCell.Value & " Sheet"
If ActiveCell.Value Like "*[!A-Za-z0-9]*" Then
WhereToGo = "'" & ActiveCell.Value & "'!A1"
Else
WhereToGo = ActiveCell.Value & "!A1"
End If
Range(Cells(iRow, iStartCol), Cells(iRow, iEndCol)).Select

.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=WhereToGo, ScreenTip:=ScreenTipMsg
End With

ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < " " Then
KeepGoing = "N"
End If

Loop

End Sub

--
Regards,
Tom Ogilvy

Thanks to this group (Brian Wilson) many years ago, I have someVBA
code which goes through a multi-tab workbook and creates a list of
hyperlinks on the first sheet which go directly to any of the tabs.
There are about 20 tabs, so you can't see them all at once, and the
hyperlinking is really useful.

My problem is that if a tab name has any special characters or spaces
in it, then thevba-generatedhyperlinkdoesn't work. If I manually
go into "edithyperlink" and click on the tab name, then it works
fine.

Thehyperlinkto tab DOCS works fine, and the one to tab ADM-SRC does
not work -- it says "reference is not valid".

I'm using Excel 2003.

Here's thevbacode:

Sub AddHyperlinks()
Dim ScreenTipMsg As String
Dim WhereToGo As String

Range("A7").Activate
KeepGoing = "Y"

Do While KeepGoing = "Y"

iRow = Selection.Row
iStartCol = "A"
iEndCol = "B"

With Worksheets(1)
ScreenTipMsg = "Go To " & ActiveCell.Value & " Sheet"
WhereToGo = ActiveCell.Value & "!A1"
Range(Cells(iRow, iStartCol), Cells(iRow, iEndCol)).Select

.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=WhereToGo, ScreenTip:=ScreenTipMsg
End With

ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < " " Then
KeepGoing = "N"
End If

End Sub

Thanks in advance for any help you can offer!

Nancy- Hide quoted text -

- Show quoted text -

Tom,
I tried adding the quote in front of the tab name, as yous suggested.
It gave me the same "reference is not valid". Then I added a quote on
the end, and still have the same problem. I guess I'll just make all
my tab names with only letters and numbers!

Thanks for your answer,

Nancy

 

[Notawahoo]

try changing

WhereToGo = ActiveCell.Value & "!A1"

to

WhereToGo = "'" & ActiveCell.Value & "'!A1"
Lightly tested, but this worked for me:

Sub AddHyperlinks()
Dim ScreenTipMsg As String
Dim WhereToGo As String

Range("A7").Activate
KeepGoing = "Y"

Do While KeepGoing = "Y"

iRow = Selection.Row
iStartCol = "A"
iEndCol = "B"

With Worksheets(1)

ScreenTipMsg = "Go To " & ActiveCell.Value & " Sheet"
If ActiveCell.Value Like "*[!A-Za-z0-9]*" Then
WhereToGo = "'" & ActiveCell.Value & "'!A1"
Else
WhereToGo = ActiveCell.Value & "!A1"
End If
Range(Cells(iRow, iStartCol), Cells(iRow, iEndCol)).Select

.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=WhereToGo, ScreenTip:=ScreenTipMsg
End With

ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < " " Then
KeepGoing = "N"
End If

Loop

End Sub

--
Regards,
Tom Ogilvy

Thanks to this group (Brian Wilson) many years ago, I have someVBA
code which goes through a multi-tab workbook and creates a list of
hyperlinks on the first sheet which go directly to any of the tabs.
There are about 20 tabs, so you can't see them all at once, and the
hyperlinking is really useful.

My problem is that if a tab name has any special characters or spaces
in it, then thevba-generatedhyperlinkdoesn't work. If I manually
go into "edithyperlink" and click on the tab name, then it works
fine.

Thehyperlinkto tab DOCS works fine, and the one to tab ADM-SRC does
not work -- it says "reference is not valid".

I'm using Excel 2003.

Here's thevbacode:

Sub AddHyperlinks()
Dim ScreenTipMsg As String
Dim WhereToGo As String

Range("A7").Activate
KeepGoing = "Y"

Do While KeepGoing = "Y"

iRow = Selection.Row
iStartCol = "A"
iEndCol = "B"

With Worksheets(1)
ScreenTipMsg = "Go To " & ActiveCell.Value & " Sheet"
WhereToGo = ActiveCell.Value & "!A1"
Range(Cells(iRow, iStartCol), Cells(iRow, iEndCol)).Select

.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=WhereToGo, ScreenTip:=ScreenTipMsg
End With

ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < " " Then
KeepGoing = "N"
End If

End Sub

Thanks in advance for any help you can offer!

Nancy- Hide quoted text -

- Show quoted text -

Tom
It's me again. Your solution DID work. I missed the quote ending the
tab name in your replacement line. I should have just copied-and-
pasted.

Thanks again!

Nancy

 

[Tom Ogilvy]

All I can do is give you a tested solution <g> (although as I said, lightly
tested)

Note that a sheet name that does not need single quotes does not seem to
work with them from my testing - thus the extra code to test and not apply
them when not needed.

--
Regards,
Tom Ogilvy

try changing

WhereToGo = ActiveCell.Value & "!A1"

to

WhereToGo = "'" & ActiveCell.Value & "'!A1"
Lightly tested, but this worked for me:

Sub AddHyperlinks()
Dim ScreenTipMsg As String
Dim WhereToGo As String

Range("A7").Activate
KeepGoing = "Y"

Do While KeepGoing = "Y"

iRow = Selection.Row
iStartCol = "A"
iEndCol = "B"

With Worksheets(1)

ScreenTipMsg = "Go To " & ActiveCell.Value & " Sheet"
If ActiveCell.Value Like "*[!A-Za-z0-9]*" Then
WhereToGo = "'" & ActiveCell.Value & "'!A1"
Else
WhereToGo = ActiveCell.Value & "!A1"
End If
Range(Cells(iRow, iStartCol), Cells(iRow, iEndCol)).Select

.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=WhereToGo, ScreenTip:=ScreenTipMsg
End With

ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < " " Then
KeepGoing = "N"
End If

Loop

End Sub

--
Regards,
Tom Ogilvy

Thanks to this group (Brian Wilson) many years ago, I have someVBA
code which goes through a multi-tab workbook and creates a list of
hyperlinks on the first sheet which go directly to any of the tabs.
There are about 20 tabs, so you can't see them all at once, and the
hyperlinking is really useful.

My problem is that if a tab name has any special characters or spaces
in it, then thevba-generatedhyperlinkdoesn't work. If I manually
go into "edithyperlink" and click on the tab name, then it works
fine.

Thehyperlinkto tab DOCS works fine, and the one to tab ADM-SRC does
not work -- it says "reference is not valid".

I'm using Excel 2003.

Here's thevbacode:

Sub AddHyperlinks()
Dim ScreenTipMsg As String
Dim WhereToGo As String

Range("A7").Activate
KeepGoing = "Y"

Do While KeepGoing = "Y"

iRow = Selection.Row
iStartCol = "A"
iEndCol = "B"

With Worksheets(1)
ScreenTipMsg = "Go To " & ActiveCell.Value & " Sheet"
WhereToGo = ActiveCell.Value & "!A1"
Range(Cells(iRow, iStartCol), Cells(iRow, iEndCol)).Select

.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=WhereToGo, ScreenTip:=ScreenTipMsg
End With

ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < " " Then
KeepGoing = "N"
End If

End Sub

Thanks in advance for any help you can offer!

Nancy- Hide quoted text -

- Show quoted text -

Tom
It's me again. Your solution DID work. I missed the quote ending the
tab name in your replacement line. I should have just copied-and-
pasted.

Thanks again!

Nancy