hyperlinks and sheet names with spaces

trevorc · #1 01-21-2017, 09:48 PM

Hi All, I am having ttrouble getting a hyperlink to work if a sheet name hace spaces or an Apostrophe in it. See code below...

tt is the dynamic sheet name, tt1 is a count of found items of mytext, the code in RED works fine if there are no space or Apostrophe in the sheet name. If there is the hyperlink created relates to A1 on the instructions sheet (and won't work). I have tried various ways to encapsulate the name in quotes to no avail....
Please help if you can, Thanks

Code:

 
tt = ws.Name
                    t11 = WorksheetFunction.CountIf(Worksheets("instructions").Range("A" & row18 & ":CX" & row18), myText)
                    ActiveSheet.Hyperlinks.Add Anchor:=Worksheets("instructions").Range("A" & row18), _
                                               Address:="", SubAddress:=ws.Name & "!" + Found.Address, _
                                               TextToDisplay:=tt & " " & Found.Address & " (" & t11 & ")"

NoSparks · #2 01-22-2017, 07:30 AM

Did you try

Code:

"'" & tt & "'"

trevorc · #3 01-22-2017, 12:04 PM

Thanks for the reply, that does fix the problem of the spaces in the sheet name, but not the apostrophe. But that's close enough for now, thankyou.

SubAddress:="'" & ws.Name & "'" & "!" + Found.Address

Kev · #4 01-30-2017, 08:45 AM

Another way to fix the problem would be to rename worksheets that contain non-valid characters
The VBA is currently set to display what happens via a message box - apostrophe is replaced with ~ and space replaced with _
To rename the worksheets remove the apostrophe at the beginning of this line

Code:

'ws.Name = newName

Code:

Sub remove_Spaces_and_Apostrophes()
    Dim oldName As String, newName As String, ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
    oldName = ws.Name
    newName = Replace(ws.Name, "'", "~")
    newName = Replace(newName, " ", "_")
    'build up message string
    msg = oldName & "   becomes   " & newName & vbCr & msg
    'replace the name
    'ws.Name = newName
Next

    MsgBox "Old Name   with     New Name" & vbCr & vbCr & msg

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Open an existing sheet by clicking on a cell in a master sheet	darbybrown	Excel	3	09-12-2016 05:12 PM
Populate sheet 3 with data from sheet 1 and sheet 2	speck	Excel Programming	0	01-14-2015 07:54 AM
Adding names to sheet for printing	WV8VFD	Excel	4	12-01-2013 11:02 PM
How to enter names in Resource Pool/names	pstein	Project	1	03-26-2012 07:37 AM
Word to Excel hyperlinks and spaces	gak	Word	1	09-14-2008 08:38 AM