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

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

Alright so you can do the following:

Sub get_sheet_name_into_letters()

Dim sheetname As String
Dim char As String
Dim iteration As Integer
Dim totchar As Integer
Dim myrange As Range
Dim result As Double
Dim hlinkname As String
Dim check As Integer


'this is just an example to check if the character is a letter. I just created a column with the letters of the alphabet

Set myrange = Application.Range(Cell1:="G1", Cell2:="G26")


'get the sheet name

sheetname = ActiveSheet.Name

sheetname = WorksheetFunction.Substitute(sheetname, " ", "")


'this loop checks whether the

iteration = 1
totchar = Len(sheetname)

'this sets the first variables
char = Mid(sheetname, iteration, 1)
hlinkname = char
iteration = iteration + 1

    Do Until iteration > totchar
            check = Asc(char)
                
            If Asc(char) > 122 Or Asc(char) < 65 Then
                char = ""
            Else
                char = Mid(sheetname, iteration, 1)
            End If
      
            
            hlinkname = hlinkname & char
            iteration = iteration + 1
            char = Mid(sheetname, iteration, 1)
        
    Loop



End Sub

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.

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!