【Excel】ピボットテーブルのスライサー機能を使って、ツールUIを作成する #VBA

実現したい事

“複数条件をユーザーライクに同時選択出来る設定オプション”をExcelツールのUI上に配置したい。

対応策の検討

以下の対応策を思いつきましたが、使いやすさの観点で、それぞれネックがあるなと思いました、、

チェックボックスを配置する -->条件が多いと配置が面倒。今後条件が増えた時の更新作業が煩雑
リストボックスを配置する -->条件が多いと縦方向に長くなり、複数条件を選ぶのが面倒
ユーザーフォームを作成する -->フォーム作成が面倒。フォームを開くという1STEP作業導線が増える

「今後の管理・更新作業が楽に出来る」「複数条件を選択・設定しやすい」方法は無いかと考えた所、ピボットテーブルのスライサー機能が見た目的に一番イメージに近かったため、UI作成に取り入れてみる事にしました。

設定手順

1.EXCELシート上に条件項目を入力する

2.入力項目範囲を“テーブル化”する

※テーブル化しておくと、後で項目追加した時に、ピボットのデータ参照範囲が自動で更新されるので、管理と更新作業が楽になります。

3.上記設定範囲を選択し、スライサーを追加する

ピボットテーブルを追加し“行”に項目を設定

［挿入］→［スライサー］と遷移し、項目を選択する

スライサーが配置されるので、スライサーを切り取り、UI画面用のシート上に配置します。

スライサーの設定項目は、VBAコードでパラメータ取得が可能です。
※SlicerCaches内のスライサー名を【スライサー_項目名】と指定する必要がある点にご注意ください。

Sub 実行()

Dim S1 As Worksheet
Set S1 = ActiveSheet

Dim Sitem As SlicerItem
Dim Val As String
Val = ""

With ActiveWorkbook.SlicerCaches("スライサー_おせち料理の品目")
    For Each Sitem In .SlicerItems
        If Sitem.Selected = True Then
            If Val = "" Then
                Val = "注文したおせち料理の品目は" & Chr(10) & Sitem.Name
            Else
                Val = Val & Chr(10) & Sitem.Name
            End If
        End If
    Next
End With

If Val = "" Then
    MsgBox "品目を指定してください"
Else
    MsgBox Val
End If
End Sub

※プログラム実行結果

まとめ

本来の使用目的とは異なる機能を流用しており、最適解では無い気もしますが、、
「複数項目指定をユーザーライクに行えるようにしたい」場合の一案になるかと思い、
設定手順含め、記事に書き起こした次第です。