入力規則リストを矢印ボタンで切り替えるExcel VBA汎用プロシージャ

概要

EventShowMoveSpinButton_Valueは、Excel VBAで指定範囲内のセルを選択したときだけ、選択セルの下に左右のShapeボタンを表示する汎用プロシージャです。

以前追加した数値を増減する移動スピンボタンの姉妹版で、今回は数値を1ずつ増減するのではなく、セルに設定された入力規則リストの値を前後へ切り替えます。

たとえば、入力規則に未対応,対応中,完了が設定されているセルなら、右ボタンで未対応から対応中へ、左ボタンで完了から対応中へ戻せます。

使う場面

ステータス、区分、評価、工程などをリストから選ばせたい
セルの入力規則ドロップダウンを開かずに、左右ボタンで候補を切り替えたい
表形式の入力画面で、選択中セルだけに操作ボタンを出したい
数値増減版と同じUIで、文字列や分類値も扱いたい
入力ミスを減らしつつ、Excelシート上の操作感を上げたい

デモ

対象範囲内のセルを選択すると、セルの下に左右ボタンが表示されます。セルに設定された入力規則リストを読み取り、クリックで前後の候補へ切り替えます。

処理イメージ図

この部品は、表示用のプロシージャ、クリック時の値変更、入力規則リスト取得、配列内検索を組み合わせて動かします。

EventShowMoveSpinButton_Valueの階層構造 — 入力規則リスト切替版の依存関係。既存の図形操作部品に、入力規則リスト取得と配列内検索を組み合わせています。

プロシージャの階層構造

EventShowMoveSpinButton_Value
├─ GetShapeByName
├─ DrawRectangleRound
├─ Set__FontColor
├─ Move__UpDownButton
├─ ChangeDownValue_MoveSpinButton
│  ├─ ChangePushedButtonColor
│  ├─ GetSelectionCell
│  ├─ ModifyTargetCount
│  ├─ GetCellValidation
│  │  └─ ConvArray1D_Start1
│  └─ Change__CellValueByList
│     └─ GetNumFromArray1D
└─ ChangeUpValue_MoveSpinButton
   ├─ ChangePushedButtonColor
   ├─ GetSelectionCell
   ├─ ModifyTargetCount
   ├─ GetCellValidation
   │  └─ ConvArray1D_Start1
   └─ Change__CellValueByList
      └─ GetNumFromArray1D

中心になるのはEventShowMoveSpinButton_Valueです。選択セルの位置にボタンを作成・移動し、ボタンクリック時の処理をChangeDownValue_MoveSpinButtonとChangeUpValue_MoveSpinButtonへ渡します。

使用例

シートモジュールのWorksheet_SelectionChangeイベントから呼び出します。下の例では、B2:B20の中を選択したときだけ、入力規則リスト切替用の左右ボタンを表示します。

使用例

vba

コードを読み込み中...

CellAreaには、ボタンを表示したいRangeを渡します。対象範囲外を選択すると、表示済みのボタンは削除されます。

サンプルブック

動作確認用のマクロ有効ブックを用意しています。

EventShowMoveSpinButton_Valueサンプルブックをダウンロード

サンプルを開くときは、Excel側でマクロを有効化してください。会社PCなどでマクロ実行が制限されている場合は、ローカルのテスト環境で確認するのが安全です。

コピー可能な実装コード

このコードには、中心処理と関連する子プロシージャをまとめて含めています。まず動きを確認したい場合は、この一式を標準モジュールへ入れて試すのが分かりやすいです。

EventShowMoveSpinButton_Value.bas

vba

コードを読み込み中...

初心者向けコード解説

EventShowMoveSpinButton_Value(ByRef Target As Range, ByRef CellArea As Range)では、現在選択されたセルTargetと、ボタンを表示したい範囲CellAreaを受け取ります。

選択セルが対象範囲内であれば、セルの下に左右ボタンを表示します。ボタンは毎回作り直すのではなく、既存の「減少ボタン」「増加ボタン」を取得し、選択セルの位置へ移動させます。どちらか片方だけ残っている場合は、一度削除して作り直すようにしています。

ボタンにはOnActionでChangeDownValue_MoveSpinButtonとChangeUpValue_MoveSpinButtonを割り当てています。クリックされると、現在選択中のセルを取得し、そのセルの入力規則リストをGetCellValidationで一次元配列として取り出します。

値の変更では、GetNumFromArray1Dで現在値がリストの何番目かを調べ、左ボタンなら1つ前、右ボタンなら1つ次の値をセルへ入れます。

注意点

対象セルにはリスト形式の入力規則が設定されている必要があります。
入力規則がリスト形式でない場合、値変更処理は行いません。
GetCellValidationは、カンマ区切りリストと範囲参照の入力規則を想定しています。
項目名そのものにカンマを含むリストには向きません。
別シート参照や名前定義を使った入力規則では、参照式の書き方によって調整が必要になる場合があります。
ボタン名として「減少ボタン」「増加ボタン」を使います。同名の図形を別用途で置かないようにしてください。

軽いまとめ

EventShowMoveSpinButton_Valueを使うと、Excelの入力規則リストを、ドロップダウンではなく左右ボタンで切り替えられるようになります。

数値増減版と同じ考え方で、選択セルに追従する2つの図形ボタンを使い回すため、シート上に大量のボタンを置かずに入力補助UIを作れます。ステータス入力や区分選択のような、候補値を順番に切り替えたい場面で使いやすい部品です。