Hyperlink Question

[Phil Osman]

I have the following code:

Sub sheets_list()
Range("M1:M100").Select
Selection.ClearContents
Range("M1").Select
r = ActiveCell.Row
s = ActiveCell.Column
For a = 0 To Sheets.Count - 1
Cells(r + a, s).Value = Sheets(a + 1).Name
Next a
End Sub

It returns a list of all sheets in my workbook.
Is it possible to amend the code so that it returns each of the sheet names
as a hyperlink to the corresponding sheet ?

TIA,
Phil

[Jim Cone]

Phil,

I assume you have the list on the last sheet in the workbook.
With some modification to your code, I came up with the following.
Note: that Xl97 hyperlink code would be slightly different and
that you cannot hyperlink to a chart sheet...
'--------------------------------------------------
Sub sheets_list()
Dim a As Long
Dim s As Long
Dim shtName As String
s = 13
For a = 1 To Sheets.Count - 1
shtName = Sheets(a).Name
Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _
& shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
Next a
End Sub
'------------------------------------------------
Regards,
Jim Cone
San Francisco, USA



"Phil Osman" <[email protected]> wrote in message
news:[email protected]...
> I have the following code:
>
> Sub sheets_list()
> Range("M1:M100").Select
> Selection.ClearContents
> Range("M1").Select
> r = ActiveCell.Row
> s = ActiveCell.Column
> For a = 0 To Sheets.Count - 1
> Cells(r + a, s).Value = Sheets(a + 1).Name
> Next a
> End Sub
>
> It returns a list of all sheets in my workbook.
> Is it possible to amend the code so that it returns each of the sheet names
> as a hyperlink to the corresponding sheet ?
>
> TIA,
> Phil

[Phil Osman]

Jim,

That works well, but the sheet I am returning the list to is the 2nd sheet
in the workbook. Maybe because of this the macro does not return the last
sheet in the workbook.
Any ideas?

Phil

"Jim Cone" wrote:

> Phil,
>
> I assume you have the list on the last sheet in the workbook.
> With some modification to your code, I came up with the following.
> Note: that Xl97 hyperlink code would be slightly different and
> that you cannot hyperlink to a chart sheet...
> '--------------------------------------------------
> Sub sheets_list()
> Dim a As Long
> Dim s As Long
> Dim shtName As String
> s = 13
> For a = 1 To Sheets.Count - 1
> shtName = Sheets(a).Name
> Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _
> & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
> Next a
> End Sub
> '------------------------------------------------
> Regards,
> Jim Cone
> San Francisco, USA
>
>
>
> "Phil Osman" <[email protected]> wrote in message
> news:[email protected]...
> > I have the following code:
> >
> > Sub sheets_list()
> > Range("M1:M100").Select
> > Selection.ClearContents
> > Range("M1").Select
> > r = ActiveCell.Row
> > s = ActiveCell.Column
> > For a = 0 To Sheets.Count - 1
> > Cells(r + a, s).Value = Sheets(a + 1).Name
> > Next a
> > End Sub
> >
> > It returns a list of all sheets in my workbook.
> > Is it possible to amend the code so that it returns each of the sheet names
> > as a hyperlink to the corresponding sheet ?
> >
> > TIA,
> > Phil
>

[Jim Cone]

Phil,

The last sheet is purposely omitted in this line...
For a = 1 To Sheets.Count - 1

Remove the "-1" to include the last sheet.

Regards,
Jim Cone
San Francisco, USA


"Phil Osman" <[email protected]> wrote in message
news:[email protected]...
> Jim,
>
> That works well, but the sheet I am returning the list to is the 2nd sheet
> in the workbook. Maybe because of this the macro does not return the last
> sheet in the workbook.
> Any ideas?
>
> Phil
>
> "Jim Cone" wrote:
>
> > Phil,
> > I assume you have the list on the last sheet in the workbook.
> > With some modification to your code, I came up with the following.
> > Note: that Xl97 hyperlink code would be slightly different and
> > that you cannot hyperlink to a chart sheet...
> > '--------------------------------------------------
> > Sub sheets_list()
> > Dim a As Long
> > Dim s As Long
> > Dim shtName As String
> > s = 13
> > For a = 1 To Sheets.Count - 1
> > shtName = Sheets(a).Name
> > Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _
> > & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
> > Next a
> > End Sub
> > '------------------------------------------------
> > Regards,
> > Jim Cone
> > San Francisco, USA
> >
> >
> >
> > "Phil Osman" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have the following code:
> > >
> > > Sub sheets_list()
> > > Range("M1:M100").Select
> > > Selection.ClearContents
> > > Range("M1").Select
> > > r = ActiveCell.Row
> > > s = ActiveCell.Column
> > > For a = 0 To Sheets.Count - 1
> > > Cells(r + a, s).Value = Sheets(a + 1).Name
> > > Next a
> > > End Sub
> > > It returns a list of all sheets in my workbook.
> > > Is it possible to amend the code so that it returns each of the sheet names
> > > as a hyperlink to the corresponding sheet ?
> > >
> > > TIA,
> > > Phil
> >

[Phil Osman]

Works perfectly now, thanks indeed Jim !
========

"Jim Cone" wrote:

> Phil,
>
> The last sheet is purposely omitted in this line...
> For a = 1 To Sheets.Count - 1
>
> Remove the "-1" to include the last sheet.
>
> Regards,
> Jim Cone
> San Francisco, USA
>
>
> "Phil Osman" <[email protected]> wrote in message
> news:[email protected]...
> > Jim,
> >
> > That works well, but the sheet I am returning the list to is the 2nd sheet
> > in the workbook. Maybe because of this the macro does not return the last
> > sheet in the workbook.
> > Any ideas?
> >
> > Phil
> >
> > "Jim Cone" wrote:
> >
> > > Phil,
> > > I assume you have the list on the last sheet in the workbook.
> > > With some modification to your code, I came up with the following.
> > > Note: that Xl97 hyperlink code would be slightly different and
> > > that you cannot hyperlink to a chart sheet...
> > > '--------------------------------------------------
> > > Sub sheets_list()
> > > Dim a As Long
> > > Dim s As Long
> > > Dim shtName As String
> > > s = 13
> > > For a = 1 To Sheets.Count - 1
> > > shtName = Sheets(a).Name
> > > Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _
> > > & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
> > > Next a
> > > End Sub
> > > '------------------------------------------------
> > > Regards,
> > > Jim Cone
> > > San Francisco, USA
> > >
> > >
> > >
> > > "Phil Osman" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have the following code:
> > > >
> > > > Sub sheets_list()
> > > > Range("M1:M100").Select
> > > > Selection.ClearContents
> > > > Range("M1").Select
> > > > r = ActiveCell.Row
> > > > s = ActiveCell.Column
> > > > For a = 0 To Sheets.Count - 1
> > > > Cells(r + a, s).Value = Sheets(a + 1).Name
> > > > Next a
> > > > End Sub
> > > > It returns a list of all sheets in my workbook.
> > > > Is it possible to amend the code so that it returns each of the sheet names
> > > > as a hyperlink to the corresponding sheet ?
> > > >
> > > > TIA,
> > > > Phil
> > >
>