Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA-generated Hyperlink problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA-generated Hyperlink problem
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 "Notawahoo" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA-generated Hyperlink problem
On Feb 13, 3:04 pm, "Tom Ogilvy" wrote:
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 "Notawahoo" wrote in message oups.com... 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 Loop 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA-generated Hyperlink problem
On Feb 13, 3:04 pm, "Tom Ogilvy" wrote:
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 "Notawahoo" wrote in message oups.com... 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 Loop 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA-generated Hyperlink problem
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 "Notawahoo" wrote in message ups.com... On Feb 13, 3:04 pm, "Tom Ogilvy" wrote: 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 "Notawahoo" wrote in message oups.com... 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 Loop 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem in changing formula-generated values in the chart in Micro | New Users to Excel | |||
Please Help - Hyperlink Problem | Excel Discussion (Misc queries) | |||
Interesting problem with VBA generated textbox (or textframe of shape) length | Excel Programming | |||
Problem with Blocking Dynamically generated UserForm Call | Excel Programming | |||
Hyperlink problem | Excel Programming |