Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hyperlink Question
Change
SubAddress:=ws.Name & "!A1 to SubAddress:="'" & wsName & "'!A1" Note that I have put -- doublequote singlequote doublequote & <-- before swName and in "!A1" I have added a singlequote after the first doublequote. Regards, Tom Ogilvy onliner wrote in message news:hMfQa.925$Ze.806@fed1read03... The following code creates a Table of Contents complete with hyperlinks to each of the worksheets within the workbook. The problem is that the hyperlinks only work for sheets with one name, for example, "sheet1". For sheets with multi-word names like "sheet one", the hyperlinks don't work. Anyone know how to fix this? Sub TableOfContents2() Dim ws As Worksheet, wsTOC As Worksheet Dim r As Long Application.ScreenUpdating = False Set wsTOC = ActiveWorkbook.Worksheets.Add (befo=ActiveWorkbook.Sheets(1)) wsTOC.Name = "Table of Contents" wsTOC.Range("A1") = "Table of Contents" wsTOC.Range("A1").Font.Size = 18 r = 3 For Each ws In ActiveWorkbook.Worksheets If ws.Name < wsTOC.Name Then wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 1), Address:="", _ SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name r = r + 1 End If Next Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hyperlink Question
Thanks, Tom.
Did you get a chance to test this? I can't get it to work. Appreciate your feedback. "Tom Ogilvy" wrote in message ... Change SubAddress:=ws.Name & "!A1 to SubAddress:="'" & wsName & "'!A1" Note that I have put -- doublequote singlequote doublequote & <-- before swName and in "!A1" I have added a singlequote after the first doublequote. Regards, Tom Ogilvy onliner wrote in message news:hMfQa.925$Ze.806@fed1read03... The following code creates a Table of Contents complete with hyperlinks to each of the worksheets within the workbook. The problem is that the hyperlinks only work for sheets with one name, for example, "sheet1". For sheets with multi-word names like "sheet one", the hyperlinks don't work. Anyone know how to fix this? Sub TableOfContents2() Dim ws As Worksheet, wsTOC As Worksheet Dim r As Long Application.ScreenUpdating = False Set wsTOC = ActiveWorkbook.Worksheets.Add (befo=ActiveWorkbook.Sheets(1)) wsTOC.Name = "Table of Contents" wsTOC.Range("A1") = "Table of Contents" wsTOC.Range("A1").Font.Size = 18 r = 3 For Each ws In ActiveWorkbook.Worksheets If ws.Name < wsTOC.Name Then wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 1), Address:="", _ SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name r = r + 1 End If Next Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hyperlink Question
Table of Contents
Sheet5 Master Sheet 3 Sheet one Master (2) Master (3) is an example and all the hyperlinks worked. -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... Yes, before posted, I did and it worked fine. Perhaps you are not making the change correctly Here is the whole routine - copy and paste it into a new module zand test it Sub TableOfContents2_AAA() Dim ws As Worksheet, wsTOC As Worksheet Dim r As Long Application.ScreenUpdating = False Set wsTOC = ActiveWorkbook.Worksheets.Add _ (befo=ActiveWorkbook.Sheets(1)) wsTOC.Name = "Table of Contents" wsTOC.Range("A1") = "Table of Contents" wsTOC.Range("A1").Font.Size = 18 r = 3 For Each ws In ActiveWorkbook.Worksheets If ws.Name < wsTOC.Name Then wsTOC.Hyperlinks.Add anchor:= _ wsTOC.Cells(r, 1), Address:="", _ SubAddress:="'" & ws.Name & "'!A1", _ TextToDisplay:=ws.Name 'wsTOC.Cells(r, 1).Value = ws.Name r = r + 1 End If Next Application.ScreenUpdating = True End Sub I tested it in Excel 97 which did not have the TextToDisplay argument, so I used the value of the cell, but I have commented out that line and restored the TextToDisplay argument. -- Regards, Tom Ogilvy onliner wrote in message news:53iQa.942$Ze.503@fed1read03... Thanks, Tom. Did you get a chance to test this? I can't get it to work. Appreciate your feedback. "Tom Ogilvy" wrote in message ... Change SubAddress:=ws.Name & "!A1 to SubAddress:="'" & wsName & "'!A1" Note that I have put -- doublequote singlequote doublequote & <-- before swName and in "!A1" I have added a singlequote after the first doublequote. Regards, Tom Ogilvy onliner wrote in message news:hMfQa.925$Ze.806@fed1read03... The following code creates a Table of Contents complete with hyperlinks to each of the worksheets within the workbook. The problem is that the hyperlinks only work for sheets with one name, for example, "sheet1". For sheets with multi-word names like "sheet one", the hyperlinks don't work. Anyone know how to fix this? Sub TableOfContents2() Dim ws As Worksheet, wsTOC As Worksheet Dim r As Long Application.ScreenUpdating = False Set wsTOC = ActiveWorkbook.Worksheets.Add (befo=ActiveWorkbook.Sheets(1)) wsTOC.Name = "Table of Contents" wsTOC.Range("A1") = "Table of Contents" wsTOC.Range("A1").Font.Size = 18 r = 3 For Each ws In ActiveWorkbook.Worksheets If ws.Name < wsTOC.Name Then wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 1), Address:="", _ SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name r = r + 1 End If Next Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hyperlink Question
Thanks, Tom.
Worked perfectly. "Tom Ogilvy" wrote in message ... Table of Contents Sheet5 Master Sheet 3 Sheet one Master (2) Master (3) is an example and all the hyperlinks worked. -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... Yes, before posted, I did and it worked fine. Perhaps you are not making the change correctly Here is the whole routine - copy and paste it into a new module zand test it Sub TableOfContents2_AAA() Dim ws As Worksheet, wsTOC As Worksheet Dim r As Long Application.ScreenUpdating = False Set wsTOC = ActiveWorkbook.Worksheets.Add _ (befo=ActiveWorkbook.Sheets(1)) wsTOC.Name = "Table of Contents" wsTOC.Range("A1") = "Table of Contents" wsTOC.Range("A1").Font.Size = 18 r = 3 For Each ws In ActiveWorkbook.Worksheets If ws.Name < wsTOC.Name Then wsTOC.Hyperlinks.Add anchor:= _ wsTOC.Cells(r, 1), Address:="", _ SubAddress:="'" & ws.Name & "'!A1", _ TextToDisplay:=ws.Name 'wsTOC.Cells(r, 1).Value = ws.Name r = r + 1 End If Next Application.ScreenUpdating = True End Sub I tested it in Excel 97 which did not have the TextToDisplay argument, so I used the value of the cell, but I have commented out that line and restored the TextToDisplay argument. -- Regards, Tom Ogilvy onliner wrote in message news:53iQa.942$Ze.503@fed1read03... Thanks, Tom. Did you get a chance to test this? I can't get it to work. Appreciate your feedback. "Tom Ogilvy" wrote in message ... Change SubAddress:=ws.Name & "!A1 to SubAddress:="'" & wsName & "'!A1" Note that I have put -- doublequote singlequote doublequote & <-- before swName and in "!A1" I have added a singlequote after the first doublequote. Regards, Tom Ogilvy onliner wrote in message news:hMfQa.925$Ze.806@fed1read03... The following code creates a Table of Contents complete with hyperlinks to each of the worksheets within the workbook. The problem is that the hyperlinks only work for sheets with one name, for example, "sheet1". For sheets with multi-word names like "sheet one", the hyperlinks don't work. Anyone know how to fix this? Sub TableOfContents2() Dim ws As Worksheet, wsTOC As Worksheet Dim r As Long Application.ScreenUpdating = False Set wsTOC = ActiveWorkbook.Worksheets.Add (befo=ActiveWorkbook.Sheets(1)) wsTOC.Name = "Table of Contents" wsTOC.Range("A1") = "Table of Contents" wsTOC.Range("A1").Font.Size = 18 r = 3 For Each ws In ActiveWorkbook.Worksheets If ws.Name < wsTOC.Name Then wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 1), Address:="", _ SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name r = r + 1 End If Next Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink Question | Excel Discussion (Misc queries) | |||
Hyperlink Question | Excel Discussion (Misc queries) | |||
Hyperlink Question | Excel Discussion (Misc queries) | |||
hyperlink question | Excel Discussion (Misc queries) | |||
Hyperlink Question | Excel Discussion (Misc queries) |