+ Reply to Thread
Results 1 to 4 of 4

Creating Hyperlink using Worksheet Name

  1. #1
    Registered User
    Join Date
    05-20-2019
    Location
    Hughenden Valley
    MS-Off Ver
    365
    Posts
    2

    Creating Hyperlink using Worksheet Name

    Hello and thanks to everyone - this is a very helpful Forum, increases my knowledge of VBA and saves hours of trial and error (although I try that first just to test what I do know )

    Anyway, this one has got me stumped .....

    I know I can't use (7?) characters in the Worksheet name i.e. \ / * [ ] : ? and obviously don't use them... ever.

    However, I'm trying to use VBA to create and insert a Hyperlink into the activeWorksheet using Worksheet's name using the following VBA macro

    Sub Insert_Link()

    SheetName = ActiveSheet.Name

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "'" & SheetName & "'!A1", TextToDisplay:="'" & SheetName & ""
    ActiveSheet.Range("A1").Font.Color = vbBlue

    End Sub


    This works well for names like John Smith.... however Sean O'Brian fails no matter how I try to get around the ' in the Worksheet name.

    This only appears to be a problem with the Hyperlink as all other VBA macro's I use are unaffected

    Does anyone know how to overcome this??

    Regards, George

  2. #2
    Registered User
    Join Date
    03-20-2019
    Location
    costa rica
    MS-Off Ver
    windows 10
    Posts
    10

    Re: Creating Hyperlink using Worksheet Name

    You could run a small macro, that checks each character on the sheet's name. Then substitute anything that is not a letter from the alphabet, with nothing.

    So that when the verification stops, it would create a string with just letters. I'll post a code shortly, as I am reading this at the bank

  3. #3
    Registered User
    Join Date
    03-20-2019
    Location
    costa rica
    MS-Off Ver
    windows 10
    Posts
    10

    Re: Creating Hyperlink using Worksheet Name

    Alright so you can do the following:

    Please Login or Register  to view this content.
    This should help you get any sheet name with letters in it, ignore special characters and get you the string to create the hyperlink. Hope this helps or guides you to getting it.

  4. #4
    Registered User
    Join Date
    05-20-2019
    Location
    Hughenden Valley
    MS-Off Ver
    365
    Posts
    2

    Re: Creating Hyperlink using Worksheet Name

    Hi drbacon,

    Many thanks for the your suggested approach to the problem I'm having with using the character ' in a Worhsheet name.

    This I hadn't considered and I'll certainly give it a try.

    Cheers!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help with creating hyperlink
    By reay21 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-16-2016, 11:59 AM
  2. [SOLVED] Creating a hyperlink to another worksheet based on IF formula
    By dosydos in forum Excel General
    Replies: 4
    Last Post: 03-01-2016, 03:42 PM
  3. Creating a hyperlink on a seperate worksheet to a created PDF
    By zakmattin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-12-2015, 12:54 PM
  4. Replies: 3
    Last Post: 01-13-2012, 12:11 PM
  5. Hyperlink Creating
    By kmoon1968 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2011, 10:55 PM
  6. CREATING HYPERLINK IN EXCEL
    By STARFINANCIALGROUP in forum Excel General
    Replies: 0
    Last Post: 01-26-2006, 01:50 PM
  7. [SOLVED] Help with creating hyperlink via VBA
    By FrigidDigit in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-19-2005, 02:05 PM

Tags for this Thread

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