+ Reply to Thread
Results 1 to 5 of 5

hyperlink to a sheet with a name defined by a variable

  1. #1
    Registered User
    Join Date
    12-07-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2010
    Posts
    3

    hyperlink to a sheet with a name defined by a variable

    I am trying to write a macro that will rename a sheet to a user provided name and then hyperlink to the to the new name

    The hyperlink does not seem to recognize the variable name as a sheet name

    here is the relevant part of the code:

    TAB_NAME = InputBox("NEW TAB NAME?")

    Sheets("TEMPLATE (2)").Select
    Range("A1:K1").Select
    ActiveCell.FormulaR1C1 = TAB_NAME
    Range("A2").Select
    Sheets("TEMPLATE (2)").Name = TAB_NAME

    Sheets("PROJECT LIST").Select
    Range("A5").Select
    Selection.Hyperlinks(1).SubAddress = "TAB_NAME!A1"

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: hyperlink to a sheet with a name defined by a variable

    crickertsen,

    Welcome to the forum! In the future, please wrap your code in code tags. See link my sig for how.
    As for your question, it's because you need to join the text string to the !A1 to create an actual cell address. Otherwise the compiler doesn't see the variable Tab_Name, it sees the text string "TAB_NAME":
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    12-07-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: hyperlink to a sheet with a name defined by a variable

    That works great, thank you. If TAB_NAME has spaces the link fails - is there a way to address that?

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: hyperlink to a sheet with a name defined by a variable

    When a tab name has a space in it, you need to surround the tab name with single quotes '
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-07-2012
    Location
    Nebraska
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: hyperlink to a sheet with a name defined by a variable

    very good, thank you

+ 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