セル範囲に合わせてボタンを作成するExcel VBA汎用プロシージャ MakeCommandButtonAtCell

概要

MakeCommandButtonAtCellは、指定したRangeの位置とサイズに合わせて、ワークシート上へフォームコントロールのボタンを作成するExcel VBAの汎用関数です。

セルのLeft、Top、Width、Heightをそのまま使うため、「このセル範囲の上に実行ボタンを置きたい」という処理を短く書けます。作成したボタンにはOnActionで指定マクロを登録し、同名のShapeが既にある場合は重複作成しない構成にしています。

ここで扱うボタンは、UserForm上のCommandButtonではなく、ワークシート上へ配置するフォームコントロールのButtonです。

使う場面

帳票シートや入力シートに「実行」「登録」「更新」などのボタンを自動配置したい
テンプレートシート生成時に、セル配置とボタン配置をまとめて作りたい
複数のセル範囲へ同じ形式の操作ボタンを作成したい
アドインや共通モジュール側の処理から、各ブック内のマクロをボタンへ割り当てたい
既に作成済みのボタンを重複して増やさないようにしたい

処理イメージ図

MakeCommandButtonAtCellで指定セル範囲にボタンを作成してマクロを実行する流れ — セル範囲、表示文字、登録マクロ名、ボタン名を渡すと、指定セル範囲と同じ大きさのボタンが作成され、クリックで登録マクロを実行できます。

使用例

次の例では、B2:C3の範囲に「テスト」というボタンを作成し、クリック時にS_メッセージ表示のみを実行します。

使用例

vba

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

作成後のボタンを変数で受け取りたい場合は、戻り値のButtonをSetで受けます。戻り値を使わない場合は、Call MakeCommandButtonAtCell(...)の形でも呼び出せます。

サンプルブック

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

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

.xlsm形式のため、Excelで開くときはマクロの有効化、保護ビュー、ダウンロードファイルのブロック解除を確認してください。

コピー可能な実装コード

下のコードは、MakeCommandButtonAtCellと、重複作成の確認に使うGetShapeByNameを1つにまとめた貼り付け用パッケージです。

MakeCommandButtonAtCell.bas

vba

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

初心者向けコード解説

最初にSet Sheet = Cell.Worksheetで、ボタンを作成する対象シートを取得しています。呼び出し側でシートを別途渡さなくても、指定したセル範囲から対象シートを判断できるようにするためです。

ButtonNameが空の場合は、Captionをそのままボタン名に使います。その後、GetShapeByName(Sheet, ButtonName)で同じ名前の図形があるかを確認し、既に存在する場合はExit Functionで抜けます。これにより、同じボタンを何度も作ってしまう事故を防ぎます。

ボタン作成の中心は、次の1行です。

Set Button = Sheet.Buttons.Add(Cell.Left, Cell.Top, Cell.Width, Cell.Height)

Cell.LeftとCell.Topでセル範囲の左上位置を取り、Cell.WidthとCell.Heightで幅と高さを取ります。そのため、指定したセル範囲にぴったり重なるボタンを作成できます。

Button.OnAction = "'" & BookFullPath & "'!" & RegistMacroでは、マクロ名だけではなく、ブックのフルパス付きで実行先を登録しています。MakeCommandButtonAtCellをxlamなどの外部アドイン側へ置いた場合でも、ボタンが押されたときに対象ブック側のマクロを呼び出しやすくするためです。

最後にButton.ShapeRange.Name = ButtonNameでボタンの図形名を設定し、Set MakeCommandButtonAtCell = Buttonで作成したボタンを戻り値として返します。

注意点

ButtonNameを省略するとCaptionがボタン名になります。同じ表示名のボタンを複数作りたい場合は、必ず別々のButtonNameを指定します。
同名のShapeが既に存在する場合は何も作成せずに終了するため、戻り値はNothingになり得ます。戻り値を使う場合はIf Btn Is Nothing Thenで確認します。
RegistMacroには、実行したいプロシージャ名を正確に渡します。引数付きマクロを直接割り当てたい場合は、OnActionへ設定する文字列を別途調整します。
保護シートではボタン作成に失敗する場合があります。事前にシート保護の状態を確認してください。
ブックのフルパスをOnActionへ含めるため、ブックを移動した後は登録先の見直しが必要になる場合があります。
マクロ有効ブックやアドインとして使う場合は、Excelのマクロセキュリティ設定も確認してください。

軽いまとめ

MakeCommandButtonAtCellを用意しておくと、セル範囲に合わせた操作ボタンの作成とマクロ登録を1行で扱えるようになります。

Excelツールでは、シート上に操作ボタンを置く場面が多いため、テンプレート生成や入力画面の整備に使い回しやすい小さな汎用部品です。