Excel Hyperlink subaddress can't be forced to string type

[TTnewbie]

I'm trying to have a macro read all the sheet names in a workbook and produce a Table of Contents (TOC) in which the sheet names are also hyperlinks to the sheet.

I ran into a problem with a notebook in which sheet names were 13E1, 22E2, etc. because the hyperlink subaddress gets interpreted as a scientific number and therefore the link tries to point to sheet names like 130 or 2200 (using the above 13E1 & 22E2).

As you can see in my code below, I tried declaring the variable as type string and tried using Cstr; neither works.

Any ideas or solutions?


Sub TOC()
Dim i As Integer
Dim bTocExist As Boolean
Dim txtNewLink As String

bTocExist = False

For i = 1 To Worksheets.Count
If Worksheets(i).Name = ("-TOC-&quot Then bTocExist = True: Exit For
Next

If bTocExist = False Then
Worksheets.Add.Move Before:=Sheets(1)
ActiveSheet.Name = ("-TOC-&quot
End If

Worksheets("-TOC-&quot.Select
Worksheets("-TOC-&quot.Cells(1, 1) = "Page"
Worksheets("-TOC-&quot.Cells(1, 1).Font.Bold = True
Worksheets("-TOC-&quot.Cells(1, 2) = "SheetName"
Worksheets("-TOC-&quot.Cells(1, 2).Font.Bold = True

Sheets(2).Activate

For i = 2 To Worksheets.Count
Worksheets("-TOC-&quot.Cells(i, 1) = i - 1
Worksheets("-TOC-&quot.Cells(i, 2) = ActiveSheet.Name
txtNewLink = CStr("'" & Format(ActiveSheet.Name) & "'" & "!A1&quot
Worksheets("-TOC-&quot.Cells(i, 2).Hyperlinks.Add _
Anchor:=Worksheets("-TOC-&quot.Cells(i, 2), _
Address:="", _
SubAddress:=CStr(txtNewLink), _
TextToDisplay:=CStr(ActiveSheet.Name)

If i < Worksheets.Count Then ActiveSheet.Next.Select
Next

Worksheets(&quot;-TOC-&quot.Activate
Cells(1, 1).Select

End Sub

 

[RobBroekhuis]

Take out the Format() function in the txtNewLink assignment, and insert a single quote in the cell assignment:

Worksheets(&quot;-TOC-&quot.Cells(i, 2) = &quot;'&quot; & ActiveSheet.Name

That does the job just fine
Rob

 

[TTnewbie]

Thanks a lot Rob, it solves my problems.