Auto Worksheet index

Printable View

Hi all

yesterday i had some help with a macro to create a list of references. I would like to take this one step further an make each name in the list a hyperlink to the worksheet. The code for the hyper link i con't seem to get it to work. Does anyone have any ideas

VB Code:

Public Sub ListWrkShts()
 
    Dim i As Integer
    Dim x As Integer
    Dim sSheet As String
    Dim oWB As Workbook
    
    Range("B1").Value = "Worksheets List"
 
    x = 2
    Set oWB = ActiveWorkbook
    For i = 2 To oWB.Sheets.Count
        oWB.Sheets(1).Cells(x, 2).Value = oWB.Sheets(i).Name
        ActiveSheet.Hyperlinks.Add Anchor:=oWB.Sheets(1).Cells(x, 2),   Address:="", SubAddress:= _
        oWB.Sheets(i).Name, TextToDisplay:= _
        oWB.Sheets(i).Name
        x = x + 1
    Next
    Set oWB = Nothing
    
    Sort
    Range("A1").Select
    
End Sub

Can anyone help

Re: Auto Worksheet index

I fixed it for you :thumb:

VB Code:

Public Sub ListWrkShts()
 
    Dim i As Integer
    Dim x As Integer
    Dim sSheet As String
    Dim oWB As Workbook
    
    Range("B1").Value = "Worksheets List"
 
    x = 2
    Set oWB = ActiveWorkbook
    For i = 2 To oWB.Sheets.Count
        oWB.Sheets(1).Cells(x, 2).Value = oWB.Sheets(i).Name
        Workbooks(1).Sheets(1).Hyperlinks.Add oWB.Sheets(1).Cells(x, 2), "http://www.vbforums.com", oWB.Sheets(i).Name, oWB.Sheets(i).Name
        x = x + 1
    Next
    Set oWB = Nothing
    
    Sort
    Range("A1").Select
    
End Sub

Mar 17th, 2005, 12:52 AM
Pearso

Re: Auto Worksheet index

Yes Very good RobDog, You playing tricks on me all the links open up the vbforum. i've tried playing around with it but still can't get it to work like i want it to. :confused:
Mar 17th, 2005, 12:58 AM
RobDog888

Re: Auto Worksheet index

:lol: Change the "http://www.vbforums.com" parameter to whereever you need (cell, worksheet, website, etc).
Mar 17th, 2005, 01:43 AM
Pearso

Re: Auto Worksheet index
This is what i tried
VB Code:
```
Workbooks(1).Sheets(1).Hyperlinks.Add oWB.Sheets(1).Cells(x, 2), "oWB.Sheets(i).Name", oWB.Sheets(i).Name, oWB.Sheets(i).Name
```
And when i click on a link it comes up with "can not open the specified file".

I'm not sure what to do next????
:cry:

What i want to do is create a list of worksheet names (which i can do) turn them into hyperlinks to the worksheet they resprent and then sort them into abc order.

Re: Auto Worksheet index

Do it without the double quotes around the object.
VB Code:
Workbooks(1).Sheets(1).Hyperlinks.Add oWB.Sheets(1).Cells(x, 2), oWB.Sheets(i).Name, oWB.Sheets(i).Name, oWB.Sheets(i).Name