【VBA】セルの内容に応じて、シートの表示／非表示を切り替える

2018年7月24日

今回は、セルの入力内容に応じて、シートの表示／非表示を制御するという方法を紹介したいと思います。

デモ

次のURLからエクセルブックをダウンロードしてください
→ControlVisibilityOfSheets.xlsm のダウンロードはこちら

エクセルブックを開き、「表示・非表示」シートのB3セル～B6セルの内容を変更すると、それに連動して、対応するシートが表示（または非表示）に切り替わります。

なぜ、チェックボックスを使わないの？

一番、大きな理由は、単純に、面倒くさいからです(^^);

また、実用を考えても、セルの値を参照するプログラムには、次のようなメリットがあります。

• 通常のセルの値の読み書きの処理を流用できるのでVBAの処理が楽
• データの増減があった場合（たとえばシートが増減した場合）のメンテナンスが楽
• （入力者にとって）チェックボックスを使うよりも、セルの編集をするほうが様々な機能が使えて便利

ソースコード

「表示・非表示シート」に、次のソースを入力します。

ソースコードを入力する場所

ソースコード

Const ROW_MIN = 3
Const ROW_MAX = 6
Const COL_INPUT = 2
Const COL_SHEETNAME = 3

Const VALUE_VISIBLE = "X"

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim configRange As Range
  Set configRange = Range(Cells(ROW_MIN, COL_INPUT), Cells(ROW_MAX, COL_SHEETNAME))
  If Intersect(Target, configRange) Is Nothing Then Exit Sub
  
  Dim Row As Long
  Dim targetSheet As Worksheet
  For Row = ROW_MIN To ROW_MAX
    Set targetSheet = ThisWorkbook.Worksheets(Cells(Row, COL_SHEETNAME).Value)
  
    If Cells(Row, COL_INPUT) = VALUE_VISIBLE Then
      targetSheet.Visible = xlSheetVisible
    Else
      targetSheet.Visible = xlSheetHidden
    End If
  Next

End Sub

解説

ソースコードの解説をしていきます。

Changeイベントで、セルの変更を検知

Private Sub Worksheet_Change(ByVal Target As Range)

セルの入力内容の変化に連動して、シートの表示・非表示の状態を変更できるようにするため、Worksheet_Changeイベントを使います。

制御用の表に変更があったかを判定。変更ない場合は実行を中断

  Dim configRange As Range
  Set configRange = Range(Cells(ROW_MIN, COL_INPUT), Cells(ROW_MAX, COL_SHEETNAME))
  If Intersect(Target, configRange) Is Nothing Then Exit Sub

シートの表示・非表示の制御に使うのは、B3セル～C6セルのみです。

そのため、B3セル～C6セルまでの内容に変化があった場合のみ、マクロを実行したいところです。

ところが、Worksheet_Changeイベントは、該当シートの変更であれば、どのセルが変更された場合にも呼び出されます。

そこで、B3セル～C6セルに変化がなかった場合には、即座に実行を中止するようにします。

以下、この3行を、さらに詳しく説明していきます。

制御用表の「セル範囲」を変数に入れる

  Dim configRange As Range
  Set configRange = Range(Cells(ROW_MIN, COL_INPUT), Cells(ROW_MAX, COL_SHEETNAME))

Rangeオブジェクトが入る変数「configRange」に、制御用の表の範囲（B3セル～C6セル）をセットします。

なお、「ROW_MIN」「COL_INPUT」「ROW_MAX」「COL_SHEETNAME」は、ソースコードの先頭で、次のように定義されています。

Const ROW_MIN = 3
Const ROW_MAX = 6
Const COL_INPUT = 2
Const COL_SHEETNAME = 3

制御用の表に変更があったかを判定。変更ない場合は実行を中断

  If Intersect(Target, configRange) Is Nothing Then Exit Sub

Intersectメソッドを使うと、指定した２つのセル範囲で「重なっているセル」があるかどうかを判定することができます。

そして、重なっているセルがない場合には、その結果は「Nothing」となります。

結局、上のロジックでは、変数「Target」と変数「configRange」に重なっているセルがあるかどうかを確認していることになります。

変数名	内容
Target	変更されたセル範囲
ConfigRange	B3セル～C6セル

「変更されたセル範囲」に「B3セル～C6セル」が含まれていない場合には、重なっているセルがないということで「Nothing」が得られます。その場合には、Then以降が実行され、「Exit Sub」にてプログラムの実行が終わります。

逆に、重なり部分がある場合には、「B3セル～C6セル」のどれかのセルが変更された、ということになりますので、次の行の処理に移ることになります。

制御用の表の１行ごとに処理を行う

  For Row = ROW_MIN To ROW_MAX

  

  （略）



  Next

For～Nextループです。
制御用の表の先頭行（ROW_MIN）～最終行（ROW_MAX）まで処理を繰り返します。

以下は、For～Nextループ内部の解説です。

処理対象となるシートを指定

    Set targetSheet = ThisWorkbook.Worksheets(Cells(Row, COL_SHEETNAME).Value)

処理対象となるシート名を、Forで指定された行（＝Row）の3列目（＝C列：COL_SHEETNAME）から読み取り、そのワークシートオブジェクトをtargetSheetオブジェクトに指定します。

セルの内容に応じて、表示・非表示を指定

    If Cells(Row, COL_INPUT) = VALUE_VISIBLE Then
      targetSheet.Visible = xlSheetVisible
    Else
      targetSheet.Visible = xlSheetHidden
    End If

Forで指定された行（＝Row）の2列目（＝B列：COL_INPUT）が、「X」（＝VALUE_VISIBLE）と等しい場合には、シートを表示します。

逆に、それ以外の場合には、シートを非表示にします。

なお、定数「VALUE_VISIBLE」は、次のように、このソースの上部で定義されています。

Const VALUE_VISIBLE = "X"

これで、For～Nextループの内部の処理は終わりです。
あとは、全シートに対して、同じ処理を繰り返します。

まとめ

セルに入力された値を使うことで、チェックボックスなどを操作するための特別な構文を覚えないでも、処理を書くことができます。

このような処理であれば、VBAに慣れていない人にも、そこそことっつきやすいのではないかと思います。

なお、今回、ご紹介した方法は、簡便性を重視してプログラムを組んだため、拡張性には難があります。

そのため、次のような場合には、プログラムの修正が必要ですので、ご注意ください。

• シートの枚数を増減させたい場合
• 制御用セルの場所を変更したい場合

ダウンロード

次のURLからエクセルブックをダウンロードしてください
→ControlVisibilityOfSheets.xlsm のダウンロードはこちら