Thread: [Solved] hyperlinks and sheet names with spaces
View Single Post
 
Old 01-21-2017, 09:48 PM
trevorc trevorc is offline Windows 7 32bit Office 2013
Competent Performer
  
Join Date: Jan 2017
Posts: 173
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default hyperlinks and sheet names with spaces
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 & ")"
Reply With Quote