[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Build index including links

Hi all

I have a workbook with some VBA that create an index with all the sheet names from the workbook. It also create hyperlinks to each sheet.

My problem occours when I rename a sheet after the index is build. Even if I re-build the index I get the "reference is not valid error".

I hope you guys can help me out with the code, which looks like this:

x = 11 
Sheet4.Range("B11:B34").Clear 
For Each ws In Worksheets 
    If Not ws.Name = "Template" Then ' The Template sheet is hidden and should not be included in the index
        Sheet4.Cells(x, 2) = ws.Name 
        Sheet4.Select 
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(x, 2), Address:="", SubAddress:=ws.Name, TextToDisplay:=ws.Name 
        x = x + 1 
    End If 
Next ws 

Open in new window


BR Michael
0
Exceldoctor
Asked:
Exceldoctor
  • 4
  • 3
1 Solution
 
SteveCommented:
in subAddress try adding a cell reference:

ws.name&"!A1"
0
 
ExceldoctorAuthor Commented:
Thx Barman
I just tried this. It has no effect on the sheets that returned the error. For the working links it works perfectly linking to cell A1 in the destination sheet.
0
 
SteveCommented:
What are the names of the sheets in error?
Are they charts or other non normal sheets?
Would it be possible to post the workbook here?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SteveCommented:
I have tested the following code and it seems to work fine.

x = 11
Sheet4.Range("B11:B34").Clear
For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name = "Template" Then ' The Template sheet is hidden and should not be included in the index
        Sheet4.Hyperlinks.Add Anchor:=Cells(x, 2), Address:="", SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
        x = x + 1
    End If
Next ws

Open in new window

0
 
ExceldoctorAuthor Commented:
Hi

I did a bit of testing....it seems that the problem can be eliminated if I dont use spaces when I rename the sheet. This is good news, but i would really prefer to allow the users of this sheet to use any sheet name convention they would like to.

Any ideas?
0
 
SteveCommented:
x = 11
Sheet4.Range("B11:B34").Clear
For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name = "Template" Then ' The Template sheet is hidden and should not be included in the index
        Sheet4.Hyperlinks.Add Anchor:=Sheet4.Cells(x, 2), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
        x = x + 1
    End If
Next ws

Open in new window

0
 
ExceldoctorAuthor Commented:
Hey that did the trick. Thx very much :-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now