Don Guillett has brought this to us :
On Jan 31, 11:06*am, GS wrote:
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 athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

You can put all, or restricted, sheet(s) macro in the THISWORKBOOK
module

That's exactly what I suggested, Don. Makes for easier project
management and maintenance!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc