PDA

View Full Version : link to multiple workbooks



white_flag
03-04-2011, 06:42 AM
Hello

I have the following code:


Public Sub IndexSheets()


Dim sh$
On Error Resume Next
sh = Sheets("Index").Name
On Error GoTo 0
If sh <> "" Then
Sheets("Calculation").Activate
Else
Worksheets.Add.Name = "Index"
End If

Dim ws As Worksheet
Dim iRow As Long


With Sheets("Index")
.Range("A2:A" & CStr(.Rows.Count)).ClearContents
End With

iRow = 1
For Each ws In Worksheets
If ws.Name <> "Index" Then
iRow = iRow + 1
With Sheets("Index")
.Hyperlinks.Add Anchor:=.Cells(iRow, 1), Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:="" & ws.Name
End With
End If
Next ws

End Sub

this create an index page with all workbooks.

I like to know how can be add an rutine, that will add to all workbooks an link to index woorkbook (in $K$1 )without workbook "Calculation" and "Data"

Bob Phillips
03-04-2011, 08:07 AM
Didn't really understand so I took a punt



Public Sub IndexSheets()


Dim sh$
On Error Resume Next
sh = Sheets("Index").Name
On Error GoTo 0
If sh <> "" Then
Sheets("Calculation").Activate
Else
Worksheets.Add.Name = "Index"
End If

Dim ws As Worksheet
Dim iRow As Long

With Sheets("Index")
.Range("A2:A" & CStr(.Rows.Count)).ClearContents
End With

iRow = 1
For Each ws In Worksheets
If ws.Name <> "Index" And ws.Name <> "Calculation" And ws.Name <> "Data" Then
iRow = iRow + 1
With Sheets("Index")
.Hyperlinks.Add Anchor:=.Cells(iRow, 1), Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:="" & ws.Name
End With
End If
Next ws

End Sub

mdmackillop
03-04-2011, 02:20 PM
Hi White Flag
FYI, use VBA tags rather than Code tags to format your code as shown.

white_flag
03-07-2011, 01:40 AM
good morning,

please look in attachment.
and the extra problem is wen I delete the sheet 'index'. The code will go crazy

GTO
03-07-2011, 05:41 AM
Hi Whiteflag,

As to the "going crazy" part, this is because you are using the Workbook_SheetActivate event. If Index doesn't exist, you create a sheet, at which point it becomes active, and thus, the SheetActivate event is again fired... until something goes Kaboom...


Option Explicit

Private Sub Workbook_SheetActivate(ByVal sh As Object)

IndexSheets
LinkToAllMySheets
End Sub

Public Sub IndexSheets()

Dim sh$
On Error Resume Next
sh = Sheets("Index").Name
On Error GoTo 0
If sh <> "" Then
'Sheets("Calculation").Activate
Else
Application.EnableEvents = False
Worksheets.Add.Name = "Index"
Application.EnableEvents = True
End If

Dim ws As Worksheet
Dim iRow As Long

With Sheets("Index")
.Range("A2:A" & CStr(.Rows.Count)).ClearContents
End With

iRow = 1
For Each ws In Worksheets
If ws.Name <> "Index" And ws.Name <> "Calculation" And ws.Name <> "Data" Then
iRow = iRow + 1
With Sheets("Index")
.Hyperlinks.Add Anchor:=.Cells(iRow, 1), Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:="" & ws.Name
End With
End If
Next ws
End Sub

Sub LinkToAllMySheets()
Dim sht As Worksheet
For Each sht In Sheets
sht.Hyperlinks.Add _
Anchor:=sht.Cells(1, 10), Address:="", _
SubAddress:="'" & "Index" & "'!A1", _
TextToDisplay:="" & "Index"
Next sht
End Sub

I would note that I would move the Public Subs to a Standard Module. You'll see that there were a couple of other minor issues.

Hope that helps,

Mark

white_flag
03-07-2011, 07:31 AM
Hello Mark

Thanks for help. It is working great.

One extra question:

if you put Public Sub in modules, then, Public Sub can be used everywhere in the workbook, and why necessary in modules?

GTO
03-07-2011, 07:42 AM
Hello Mark

Thanks for help. It is working great.

One extra question:

if you put Public Sub in modules, then, Public Sub can be used everywhere in the workbook, and why necessary in modules?

I hope I am understanding the question. As you say, since the Sub is Public, yes, it is accessible from another module. But since the Sub is in ThisWorkbook, you would need to preface the Sub's name with the name of the Class module that it resides in. Try this in another sub:

Sub MySub()
Call IndexSheets 'Fails if the sub IndexSheets resides in ThisWorkbook or another Class or Object Module.
Call ThisWorkbook.IndexSheets 'Succeeds
End Sub

You will see that the first Call fails at compile, because it cannot 'find' the procedure without qualifying.

You may of course leave the subs there and qualify any calls - I was simply stating my opinion of what's easiest.

Hope that helps,

Mark

white_flag
03-07-2011, 08:20 AM
so if the public sub is in Modules then


Sub MySub()
Call IndexSheets 'Success
End Sub


or not?