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
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?
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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.