【ExcelVBA】複数ボタンの処理をマクロ1つにまとめる

2022年9月17日

【ExcelVBA】複数ボタンの処理を1つのマクロにまとめる

エクセルのシート上に配置した複数のボタンに対する処理を1つのマクロにまとめたいときには、「Application.Caller」を使ってマクロを呼び出したボタン名の情報を取得しましょう。

ボタン名から、そのボタンの図形オブジェクトを取得できるので、そのボタンに書かれたテキストや位置に応じてマクロの処理を変えることができます。

ボタンの数だけVBAのマクロを準備するのは大変

似たような処理を行う複数のボタンがある場合には、VBAのプログラムは1つにまとめてしまいたいところです。

ただ、普通の方法では、ボタンの数だけVBAのマクロを準備する必要があります。

たとえば、次のように、行ごとに準備されたボタンを押すと、その行のC列の値をC2セルに表示するようなプログラムを書くことを考えてみましょう。

このとき、次のようなプログラムを準備して、B5セルのボタンに「Click1」、B6セルのボタンに「Click2」、B7セルのボタンに「Click3」を割り当てれば、目的の動作をさせることはできます。

Sub Click1()
  Range("C2").Value = Range("C5").Value
End Sub

Sub Click2()
  Range("C2").Value = Range("C6").Value
End Sub

Sub Click3()
  Range("C2").Value = Range("C7").Value
End Sub

ただし、この方法には、次のような問題点があります。

ボタン1つ1つにマクロを割り当てていくのに手間がかかる

マクロの割り当ては、右クリックメニューの「マクロの登録」から行う必要があります。

個々のボタンごとに登録する必要があるので、手間がかかります。

行数を増やしたいときにプログラムを修正する必要がある

表の行数を増やしたくなったらボタンを増やす必要があります。

そのとき、Subプロシージャもボタンの数に合わせて増やす必要があります。

ボタンを増やすごとに、Subプロシージャの数を増やすのは大変です。

「Application.Caller」で呼出元ボタン名を取得する

このように、ボタンごとにSubプロシージャを作ると、作成時、修正時ともに、手間がかかります。

そこで、Subプロシージャを共通化して、複数のボタンを1つのSubプロシージャで処理できるようにしましょう。

ここで便利なのが「Application.Caller」です。

「Application.Caller」を使うと、呼び出し元のボタンのオブジェクト名を取得できます。

挙動確認用のVBAマクロを作成する

実験のため、下記のようなプログラムを標準モジュールに作成してください。

Sub Click()
  Debug.Print Application.Caller
End Sub

複数のボタンを作成し、ボタンを押したときにこのマクロを起動するように設定しましょう。

ボタンにマクロを割り当てる

まず、ボタンを1つ作成して、そのボタンに「Click」プロシージャを割り当てます。

ボタンを複製する

このボタンを必要な数だけ複製しましょう。

すべてのボタンで起動するマクロは同じなので、単にボタンを複製するだけで適切に動くのが、今回紹介する方法の大きなメリットです。

B5セルをコピーして、B6～B7セルに貼り付けてください。

ボタンではなく、セルを選択してコピー・貼り付けするのがポイントです。

これで、ボタンが複製され、どのボタンを押しても「Click」プロシージャが起動するようになります。

Application.Callerの挙動確認

この状態で、それぞれのボタンを押すと、イミディエイトウィンドウに「押したボタンのオブジェクト名」が表示されます。

たとえば、「四角形: 角度付き 1」というのが1つ目のボタンのオブジェクト名です（挿入した図形や環境により、実際に表示されるオブジェクト名は変わります）。

これを使って、冒頭で紹介した、C2セルにボタンで選択した行のC列の値をC2セルに表示させるプログラムを書いてみましょう。

実際のプログラムに応用してみる

以下の流れでプログラムを書いていきます。

• 押されたボタンの「図形オブジェクト」を取得する
• その図形オブジェクトがある「セル」を取得する
• そのセルの「行数」を取得する
• 目的の値を取得・転記する

押されたボタンの「図形オブジェクト」を取得する

「Application.Caller」で得られた名前に対応する図形オブジェクトを取得します。

図形オブジェクトを取得するには、WorksheetオブジェクトのShapesメソッドを使います。

自動補完が効くように、いったん「ActiveSheet」をWorksheet型の変数「WS」に格納して、「WS.Shapes」の形で「Shape」オブジェクトを取得します。

Sub Click()
  '自動補完が効くようにActiveSheetをWorksheet型の変数「WS」に格納
  Dim WS As Worksheet
  Set WS = ActiveSheet

  '以下の行は、まだ書きかけ
  WS.Shapes(Application.Caller)
End Sub

※7行目は、まだ書きかけです

その図形オブジェクトがあるセルを取得する

「Shape」オブジェクトの「TopLeftCell」プロパティで、その図形の左上隅の位置にあるセル（Rangeオブジェクト）を取得できます。

 '以下の行は、まだ書きかけ
  WS.Shapes(Application.Caller).TopLeftCell

※先ほどのソースコードの一部分だけを表示しています

その図形オブジェクトがあるセルの「行数」を取得する

「Range」オブジェクトの「Row」プロパティで、そのセルの行を取得できます。

  '以下の行は、まだ書きかけ
  WS.Shapes(Application.Caller).TopLeftCell.Row

※先ほどのソースコードの一部分だけを表示しています

正しく動いているか検証するため、いったん、この内容を「Debug.Print」で表示してみましょう。

Sub Click()
  '自動補完が効くようにActiveSheetをWorksheet型の変数「WS」に格納
  Dim WS As Worksheet
  Set WS = ActiveSheet

  'いったんDebug.Printで正しく行番号が表示されるか確認
  Debug.Print WS.Shapes(Application.Caller).TopLeftCell.Row
End Sub

それぞれのボタンを押してみると、たしかに、各ボタンが置かれている「行」の値が表示されています。

目的の値を取得・転記する

ここまでくれば、セルの値を転記するだけです。

ボタンが置かれている「行」のC列の内容をC2セルに転記するロジックを付け足します。

Sub Click()
  '自動補完が効くようにActiveSheetをWorksheet型の変数「WS」に格納
  Dim WS As Worksheet
  Set WS = ActiveSheet

  '行数取得ロジックが長いので、いったん変数に格納
  dim Row
  Row = WS.Shapes(Application.Caller).TopLeftCell.Row

  '転記
  WS.Cells(2, 3).Value = WS.Cells(Row, 3).Value
End Sub

これで、ボタンに応じてC2セルに適切な値を表示されます。

たとえば、B5セルのボタンをクリックすると、C5セルの値がC2セルに転記されて「AA」と表示されます。

その他の例

先ほどのプログラムの6行目～11行目を次のように変更すると、ボタンに表示された文字を取得できます。

Sub Click()
  '自動補完が効くようにActiveSheetをWorksheet型の変数「WS」に格納
  Dim WS As Worksheet
  Set WS = ActiveSheet

  'ボタンに書かれた文字を取得
  Dim Text
  Text = WS.Shapes(Application.Caller).TextFrame.Characters.Text

  '転記
  WS.Cells(2, 3).Value = Text
End Sub

たとえば、次のように3つのボタンを作成後、このマクロを起動するように設定しましょう。

その後、「BBB」と書かれたボタンをクリックすると、C2セルに「BBB」と表示されます。

まとめ

エクセルのシート上に配置した複数のボタンに対する処理を1つのマクロにまとめたいときには、「Application.Caller」を使いましょう。

「Application.Caller」で得られた情報から、図形が配置されたセルや図形内のテキストなどの情報を取得して、処理を変えることができます。