#危うく3月は記事0になるとこでした XD
大した話ではないのですが、最近関わったQ&Aで
『ワークシートのコピーをきっかけに、マクロを実行したい』というのがありました。
ぇ?そんなオチ?って終わり方でしたけどちょっと反省点もあり、書いておきます。
Book内で新規Sheetを追加した時にマクロを走らせるには、ThisWorkbookモジュールのWorkbook_NewSheetイベントがあります。
ではBook内で既存Sheetをコピーした時のイベントを捉まえるにはどうしたらいいか。
WorkSheet.Copy時にはWorkbook_NewSheetイベントは発生しませんから、代替的にWorkbook_SheetActivateを使ったりします。
一応回答コードはこれ▼
質問では言及されてませんでしたが、あくまでコピーの時だけの話で、新規シート追加は除外したい場合を考えてみました。
Bookを開いてSheetを切り替えないままコピーしたりするケースを考えると、Workbook_Openイベントでモジュール変数にシート数をセットしておいたほうが良いような気がしますし、
新規追加時にもシート名のお尻に『(数値)』が付加されるケースを考えると、Activeになったシート名で判断するのも使えないし、
...などなど。
少し考え過ぎですね(反省)
コードの流れとしては、「シートコピー時にはWorkbook_SheetActivateイベントのみが発生し、追加時にはWorkbook_SheetActivateイベントの後にWorkbook_NewSheetイベントも発生する」
ので、これを利用する為にApplication.OnTimeメソッドを使ってます。
Workbook_SheetActivate
▼
Workbook_NewSheet
▼
OnTimeメソッドで呼び出したプロシージャ
こんな順番で実行されるようにして、途中のWorkbook_NewSheetイベントで、新規シート追加かどうかを判定するわけです。
でもまぁ、こんなややこしい事しなくても、普通はシートコピーしたらお尻に『( )』が付加されますからシート名で判定してもそれほど困らないとは思いますけどね。
ちなみに、
>新規追加時にもシート名のお尻に『(数値)』が付加されるケース
って普通は無いのですが、たまたま私の環境がそうだったりします。
新規Bookや新規Sheetを雛形のシートから作成されるような環境にしてると、そうなる事があります。
C:\Documents and Settings\(ユーザー名)\Application Data\Microsoft\Excel\XLSTART
や
C:\Program Files\Microsoft Office\Office?\XLSTART
などのスタートアップパスに Book.xlt や Sheet.xlt のテンプレートを置く事で設定できます。[win2000/xl2000]
ただ、新規シート追加時に常に『()』が付加されるわけでは無いようで、再現条件が今いち不明です。
検証不足は否めません。
もしかしたら私の環境が壊れかけてる(とは思いたくないですが)のかも -"-
大した話ではないのですが、最近関わったQ&Aで
『ワークシートのコピーをきっかけに、マクロを実行したい』というのがありました。
ぇ?そんなオチ?って終わり方でしたけどちょっと反省点もあり、書いておきます。
Book内で新規Sheetを追加した時にマクロを走らせるには、ThisWorkbookモジュールのWorkbook_NewSheetイベントがあります。
ではBook内で既存Sheetをコピーした時のイベントを捉まえるにはどうしたらいいか。
WorkSheet.Copy時にはWorkbook_NewSheetイベントは発生しませんから、代替的にWorkbook_SheetActivateを使ったりします。
一応回答コードはこれ▼
'ThisWorkbook Module Option Explicit Dim flg As Boolean Dim shCount As Long Dim shName As String '------------------------------------------------- Private Sub Workbook_NewSheet(ByVal Sh As Object) flg = True End Sub '------------------------------------------------- Private Sub Workbook_Open() shCount = Sheets.Count End Sub '------------------------------------------------- Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim n As Long n = Sheets.Count If n <> shCount Then If n > shCount Then shName = Sh.Name Application.OnTime Now, Me.CodeName & ".test" End If shCount = n End If End Sub '------------------------------------------------- Private Sub test() If flg Then flg = False Else MsgBox shName End If End Sub
質問では言及されてませんでしたが、あくまでコピーの時だけの話で、新規シート追加は除外したい場合を考えてみました。
Bookを開いてSheetを切り替えないままコピーしたりするケースを考えると、Workbook_Openイベントでモジュール変数にシート数をセットしておいたほうが良いような気がしますし、
新規追加時にもシート名のお尻に『(数値)』が付加されるケースを考えると、Activeになったシート名で判断するのも使えないし、
...などなど。
少し考え過ぎですね(反省)
コードの流れとしては、「シートコピー時にはWorkbook_SheetActivateイベントのみが発生し、追加時にはWorkbook_SheetActivateイベントの後にWorkbook_NewSheetイベントも発生する」
ので、これを利用する為にApplication.OnTimeメソッドを使ってます。
Workbook_SheetActivate
▼
Workbook_NewSheet
▼
OnTimeメソッドで呼び出したプロシージャ
こんな順番で実行されるようにして、途中のWorkbook_NewSheetイベントで、新規シート追加かどうかを判定するわけです。
でもまぁ、こんなややこしい事しなくても、普通はシートコピーしたらお尻に『( )』が付加されますからシート名で判定してもそれほど困らないとは思いますけどね。
ちなみに、
>新規追加時にもシート名のお尻に『(数値)』が付加されるケース
って普通は無いのですが、たまたま私の環境がそうだったりします。
新規Bookや新規Sheetを雛形のシートから作成されるような環境にしてると、そうなる事があります。
C:\Documents and Settings\(ユーザー名)\Application Data\Microsoft\Excel\XLSTART
や
C:\Program Files\Microsoft Office\Office?\XLSTART
などのスタートアップパスに Book.xlt や Sheet.xlt のテンプレートを置く事で設定できます。[win2000/xl2000]
ただ、新規シート追加時に常に『()』が付加されるわけでは無いようで、再現条件が今いち不明です。
検証不足は否めません。
もしかしたら私の環境が壊れかけてる(とは思いたくないですが)のかも -"-