Hyperlink to Sheets using Variable

[Von Pookie]

...or, I could just have never noticed that before. I'm not really sure at the moment.

Either way: you can edit the code to check. If the name has spaces in it, have the link entered with the quotes, otherwise without.

    'check if the text contains a space
    If InStr(1, VariableSheetName, " ") > 0 Then
        'if text contains a space, add link with single quotes
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & VariableSheetName & "'!A1", TextToDisplay:=VariableSheetName
    Else
        'if text does not contain a space, add link without quotes
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        VariableSheetName & "!A1", TextToDisplay:=VariableSheetName
    End If

Edit: Alternately, you could just not allow spaces in the names. If there are any entered, change them to an underscore, for example:

VariableSheetName = InputBox("Site Name", "Enter new Site name", "")

If InStr(1, VariableSheetName, " ") > 0 Then VariableSheetName = Replace(VariableSheetName, " ", "_")

Then you won't have to worry about the quotes for those links.