【Excel VBA】シート見出しの色を変更する

質問

Excel2003を使用しています。

シート数が10数枚のBookが複数あり、これらのBookで、シートのデータが変更されたら、シート見出しに色を付けるマクロを作りたく、とりあえず、マクロの記録をとってみました。

マクロの記録で、シート見出しの色を変更するコードや複数のシートを選択するコードはわかりましたが、実際にしたいことは、シート名が4桁の数値のシートにだけ、このマクロが実行されるようにしたいのです。このようなことは可能でしょうか？可能であれば、どのようにコードを記述すればいいでしょうか？

よろしくお願いします。

回答No.2

imogasi · ベストアンサー

1ブックの中ではシートの値（書式は反応しない、数式は値が変わり反応する）を変えたらメッセージを出すものを実験する。
VBEのVBAProjectの当該ブックの「Thisworkbook」で右クリックして「コードの表示」をだし、
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "aaa"
End Sub
を入れる。
ーー
これでどのシートでも値を変更すれば「aaa」と表示されることを確認する。
ーーー
どのシートのセルの値を変えても、このルーチンに飛んでくるから
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "aaa"
MsgBox ActiveSheet.Name
End Sub
をやってみて、シート名が取れるから、シート名文字列を４桁の数字かどうかチェックし、シートタブを色を変える。
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'--ここへシート名が数字４桁かチェックを入れる
MsgBox "aaa"
MsgBox ActiveSheet.Name
ActiveSheet.Tab.ColorIndex = 3
End Sub

お礼

回答ありがとうございます。

順を追って丁寧に説明していただいたおかげで、マクロ初心者の私でも理解することができました。
「ThisWorkbook」を右クリック → コードの表示 → SheetChangeイベントにNo.1の方のサンプルを参考にさせていただきながら、自分なりにコードを記述して試してみたのですが、エラーが出てしまい、行き詰っていたところでしたので、助かりました。

ありがとうございました。

回答No.3

zap35

＃０１です。補足への回答が遅くなりました

＞この場合、該当のシートモジュール全てにイベントプロシージャを作成するのでしょうか
普通はそうですね。でもその場合はマクロを少し変更する必要があります。各シートのモジュールシートに貼り付けるマクロは以下になります。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
　　Set sh = ActiveSheet
　　If IsNumeric(sh.Name) And Len(sh.Name) = 4 Then
　　　　sh.Tab.ColorIndex = 4
　　Else
　　　　sh.Tab.ColorIndex = xlNone
　　End If
End Sub

でもシートがたくさんあるときや、シートの追加を行う場合は不便です。そんなときは上の方法ではなくクラスモジュールを使用するのがよいでしょう。

まず次のマクロをThisWorkbookのモジュールシートに貼り付けます。
Dim myAppCls As New Class1
Sub Workbook_Open()
　　Set myAppCls.myApp = Application
End Sub

次にＶＢＥ画面で「挿入」→「クラスモジュール」で開く画面に以下を貼り付けます
Public WithEvents myApp As Application
Private Sub myApp_sheetchange(ByVal sh As Object, ByVal trget As Range)
　　If IsNumeric(sh.Name) And Len(sh.Name) = 4 Then
　　　　sh.Tab.ColorIndex = 4
　　Else
　　　　sh.Tab.ColorIndex = xlNone
　　End If
End Sub

ブックを保存して一旦閉じて、再度開いてみてください。
セルの値が変わったシートのタブの色が変わるはずです
（ただし「数字」「４桁」の条件に合っていればですが）

お礼

再度の回答ありがとうございます。

>シートがたくさんあるときや、シートの追加を行う場合は不便です。そんなときは上の方法ではなくクラスモジュールを使用するのがよいでしょう。

1つのBookのシート数は10数枚ですが、同様のBookが複数あり、シートの追加を行うこともありまして、全てのシートモジュールに同じ内容のイベント書くのは手間もかかるので、とりあえず保留にしていたところ、クラスモジュールを使用する方法を教えていただき、助かりました。
また、クラスモジュールはほとんど使用したことがなかったので、勉強になりました。

ありがとうございました！

回答No.1

zap35

マクロサンプルです。
以下のマクロをALT＋F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。マクロの実行はワークシート画面に戻ってALT＋F8でマクロ一覧を開き、マクロ名を選択して「実行」ボタンです。

Sub Macro1()
Dim ws As Worksheet
　For Each ws In Worksheets
　　If IsNumeric(ws.Name) And Len(ws.Name) = 4 Then
　　　ws.Tab.ColorIndex = 4
　　Else
　　　ws.Tab.ColorIndex = xlNone
　　End If
　Next ws
End Sub

お礼

早々の回答ありがとうございます。

教えていただいたサンプルを、実際に試してみたところ、うまくいきました。
質問文にも書いておりますが、シート(シート名が4桁の数値のシート)のデータが変更されたら、このマクロが実行されるようにしたいのですが、この場合、該当のシートモジュール全てにイベントプロシージャを作成するのでしょうか？それとも、他に何か方法があるのでしょうか？

再度の質問で恐れ入りますが、よろしくお願いします。