+ Reply to Thread
Results 1 to 19 of 19

Automatically create hyperlinks for a table of contents with VBA.

  1. #1
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Automatically create hyperlinks for a table of contents with VBA.

    Hello, I have several Workbooks that I have made a Table of Contents (TOC) for. In the TOC I have a list of the worksheets that I manually create hyperlinks for. To do this I use two modules, one is called "List_Sheets" which will make a list of the worksheets in the workbook.
    Please Login or Register  to view this content.
    The other one I use is "Auto_Open" which opens the TOC once you open the workbook.
    Please Login or Register  to view this content.
    I would like to be able to use VBA to automatically populate the hyperlinks into the listed worksheets. Below I have a mock workbook setup to show what I mean. Any help would be very appreciated.

    Auto_Hyperlink_TOC.xls
    Last edited by jakeisbill; 04-03-2013 at 02:48 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Please Login or Register  to view this content.
    Elegant Simplicity............. Not Always

  3. #3
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Thanks for the help Andy. But it seems only to link to the TOC page.

  4. #4
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Automatically create hyperlinks for a table of contents with VBA.

    It's based on your List Sheets sub Jake... The only thing i changed was that instead of listing the sheet name it hyperlinks it..Try remming out the line beginning Activesheet and see if it gives you a list of sheets..

  5. #5
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Quote Originally Posted by AndyLitch View Post
    It's based on your List Sheets sub Jake... The only thing i changed was that instead of listing the sheet name it hyperlinks it..Try remming out the line beginning Activesheet and see if it gives you a list of sheets..
    I'm Sorry Andy, but what do you mean by "Remming"?

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Insert an apostrophe (') at the beginning of the line. The text should go green and it will be ignored by the computer

  7. #7
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Yes. Your code does make the list with hyperlinks attached to them. But all of the links are to "#TOC!A1"

  8. #8
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Automatically create hyperlinks for a table of contents with VBA.

    All hyperlink in Range A1

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Quote Originally Posted by AndyLitch View Post
    Please Login or Register  to view this content.
    Thanks Andy, you're the man!

  11. #11
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Sorry AB33, that one didn't work out. It only made a bad link to the TOC page.

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Mine does in every sheet in cells(1,1)

  13. #13
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Aha. That is a nice bit of code. But I fail to see how that would be helpful to link to the page that you are already on. Thank you none the less, all advice is taken with great generosity.

  14. #14
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Well it looks like I spoke too soon. Trying this code on another worksheet it turns out that is doesn't seem to work. The links are created, and it looks like the addresses are correct, but a message box saying "Invalid Reference" comes up.

  15. #15
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Quote Originally Posted by AB33 View Post
    All hyperlink in Range A1

    Please Login or Register  to view this content.
    AB33, is there a way to amend this code to Link to sheet 1 in A1, sheet 2 in A2, sheet 3 in A3, etc...?

  16. #16
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Jake,
    Please do not reply with quote. Just reply! It has not purpose other than to clutter the space of this page.
    I thought my earlier code had created a bad link, but here we go

    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Sorry about that AB33. I tried running List_Sheets111 and got nothing.

  18. #18
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Please see attached
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Automatically create hyperlinks for a table of contents with VBA.

    Using Andy's code I think I almost have it.
    Please Login or Register  to view this content.
    The issue I have found is that when you hover over the link it reads
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I manually added a hyperlink to a sheet while recording a macro after that and got a similar but different link once I hovered over it. It read
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    but when I try to adjust the code to something like this it wont compile.
    Please Login or Register  to view this content.
    Any thoughts?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1