Scott Spence explained on 1/31/2012 :
Whys that a bad idea?
It is good to have it in the Workbook_SheetActivate event yes so it is all in
one place and easier to manage you're right.
It was a suggestion, in your example you are adding hard coded sheet names!
What if someone changes the sheet name?
Store the sheet codenames instead! Then it doesn't matter what users do
with the tab names.
Change this line...
If InStr(1, msTARGET_SHEETS, Wks.Name) 0 Then
to..
If InStr(1, msTARGET_SHEETS, Wks.CodeName) 0 Then
And change this line...
With Sheets(s): Call importSheet(WksSource): End With
to..
With Sheets(Get_SheetTabName(ActiveWorkbook, s))
Call importSheet(WksSource)
End With
...which uses the following function...
Function Get_SheetTabName(Wkb As Workbook, CodeName As String) As
String
Dim Wks As Worksheet
For Each Wks In Wkb.Worksheets
If Wks.CodeName = CodeName Then _
Get_SheetTabName = Wks.name: Exit Function
Next
End Function
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc