dcsimg
Results 1 to 3 of 3

Thread: Excel VBA: How To: Set Hyperlink to Sheet With Special Chars in Name !!!

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Resolved Excel VBA: How To: Set Hyperlink to Sheet With Special Chars in Name !!!

    Esteemed Forum Participants and Lurkers:
    ===============================
    Excel 2003 VBA

    I came across a tricky anomaly when I tried to write VBA code to create hyperlinks in a cell in one workbook to link to a cell range in a different workbook. The sheets were already named in both documents, and I just could not get the hyperlink to accept a range in the target sheet "5-12" (May 12th sheet). The resultant hyperlink would open the correct sheet, but would popup an error message: "Reference is not valid".

    I did a lot of experimenting and discovered that only a few special characters work as separators in Sheet names for creating hyperlinks ... " . _ ~ ". Spaces and hyphens "-" do NOT work. After more experimenting, I finally discovered that if you enclose the sheet name in apostrophes like this ... "'My Sheet'" ... in the hyperlink subaddress, any valid sheet name will work, whether or not it has special characters. Here is some sample code:
    Code:
        Dim LinkSht As Worksheet  'Sheet where Links are to be Installed
        Dim TargSht As Worksheet  'Target Sheet
        Dim TargRange As Range    'Target Range for HyperLink
        
        Set TargSht = Workbooks("Book2").Sheets("My Sheet-n") '... Set this to any legal sheet!
        Set TargRange = TargSht.Range("B32:F32")              '... Set this to any desired range!
        
        Set LinkSht = Workbooks("Book1").Sheets("Link Sheet")
        LinkSht.Hyperlinks.Add _
            Anchor:=LinkSht.Range("C8"), _
            Address:=Workbooks("Book2").FullName, _
            SubAddress:="'" & TargSht.Name & "'" & "!" & TargRange.Address
    You never know when this might come in handy!
    Last edited by Webtest; Mar 5th, 2011 at 11:22 AM. Reason: Corrected 2 typos in "Set TargSht = Workbooks..."
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  2. #2
    New Member
    Join Date
    Oct 2013
    Posts
    1

    Re: Excel VBA: How To: Set Hyperlink to Sheet With Special Chars in Name !!!

    This post, as old as it is, turned out to be gem for me today. Many thanks.

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Excel VBA: How To: Set Hyperlink to Sheet With Special Chars in Name !!!

    Glad you got some mileage out of this! I always try to share my "aha" moments. You never know when a little knowledge will come in handy.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width