シートの表示／非表示をもっと簡単に - Infomentのブログ～Excel VBA奮闘記～

先日 Excel について、ある相談を受けました。マクロを用いてシートの表示／非表示を行う際、もっと簡単にできないか？というものです。その時点では、こんな感じでした。

f:id:Infoment:20180723063801p:plain

各シートに対応するチェックボックスが、先頭シートに配置されています。
図中「札幌支社」のチェックボックスにチェックを入れると、同じ名前のシートが非表示になる、という仕掛けでした。

※下記は全て、チェックボックスが配置された「Sheet1」のシートモジュールに記載されています。

f:id:Infoment:20180723064321p:plain

Private Sub CheckBox_札幌支社_Click()
    If CheckBox_札幌支社 = True Then
        Sheets("札幌支社").Visible = False
    Else
        Sheets("札幌支社").Visible = True
    End If
End Sub

Private Sub CheckBox_東京支社_Click()
    If CheckBox_東京支社 = True Then
        Sheets("東京支社").Visible = False
    Else
        Sheets("東京支社").Visible = True
    End If
End Sub

Private Sub CheckBox_大阪支社_Click()
    If CheckBox_大阪支社 = True Then
        Sheets("大阪支社").Visible = False
    Else
        Sheets("大阪支社").Visible = True
    End If
End Sub

Private Sub CheckBox_博多支社_Click()
    If CheckBox_博多支社 = True Then
        Sheets("博多支社").Visible = False
    Else
        Sheets("博多支社").Visible = True
    End If
End Sub

相談内容の趣旨は、以下の解消です。

シートの増減に合わせて、チェックボックスの増減が必要
チェックボックスが増えるたび、マクロの追加が必要

正直に言えば、その時点においてこのマクロは、全然問題ない作りだと思いました。
なぜなら目的の機能を充分果たしているうえに、直感的に分かり易いと感じたからです。

しかしメンテナンスに関しては確かに、依頼者の言うとおりでした。そこで、次のように検討してみました。

クラスモジュールによる制御

チェックボックス毎に表示／非表示のコードを書くのは、非常に煩雑です。チェックボックスが100個あれば、似たようなコードを100個書く必要があるし、修正があれば100回の修正が必要になるかもしれません。

そこでまず、クラスモジュールを使った次のような制御を検討しました。

どのチェックボックスかに関わらず、とにかくチェックボックスの値が変更されたことを検知する。
値が変更されたチェックボックスの名前から判断して、同名シートの表示／非表示を切り替える。

しかし、直ぐ廃案にしました。なぜなら、依然として以下の問題が残るからです。

シートの名前だけを変更すると、正しく作動しなくなる。
シートの並びを変えた際、チェックボックスの並びも変えたくなる。

リストボックスによる制御

リストボックスにシート名を表示させ、同リスト内シート名の選択／非選択とシートの表示／非表示を同期させる案を考えてみました。

f:id:Infoment:20180723071609p:plain

なお、リストボックスは事前に、複数の項目を選択できるようにしておきます。

f:id:Infoment:20180723071706p:plain

そのうえで、シートモジュールに次のように記載しました。

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim i  As Long
    Dim Lb As Object
        Set Lb = ActiveSheet.OLEObjects("ListBox1").Object
        For i = 0 To Lb.ListCount - 1
            If Lb.Selected(i) = True Then
                Sheets(Lb.List(i)).Visible = False
            Else
                Sheets(Lb.List(i)).Visible = True
            End If
        Next
End Sub

Private Sub Worksheet_Activate()
    Dim Ws As Worksheet
    Dim Lb As Object
        Set Lb = ActiveSheet.OLEObjects("ListBox1").Object
        Lb.Clear
        For Each Ws In Worksheets
            If Ws.Name <> "Sheet1" Then
                Lb.AddItem Ws.Name
            End If
        Next
End Sub