セルにリンクしたスピンボタンをVBAで作成する

「vba セルにスピンボタン設定後にセル値を直接操作」
という検索でアクセスがありました。

この検索キーワードだけではハッキリしませが、VBAでセルにリンクしたスピンボタンを作成するにはどのようなコードを書けばいいのか、調べていらしたのでしょうか。

セルにリンクしたスピンボタンを作成するサンプルマクロ

以下のExcelマクロを実行すると、A1セルにリンクしたスピンボタンがB1セルの左端に作成されます。

作成されたスピンボタンの［コントロールの書式設定］ダイアログ－［コントロール］タブを確認すると、下図のように設定されていることを確認できます。

サンプルマクロで行っている処理

拙著『理解するExcel VBA／図形操作の基本』の「7-5. Rectangle・Oval・DrawingObjectsとは」では、RectangleやOval等のレガシーオブジェクトについて少しだけ解説しています。

Spinners・Spinnerはレガシーなオブジェクト

上記のマクロで使用しているSpinnersコレクションや

Spinnerオブジェクトは、

RectangleやOval等と同様にレガシーなオブジェクトです。

オブジェクトブラウザーで、［非表示のメンバーを表示］オプションをOnの状態にすると、SpinnersコレクションやSpinnerオブジェクト、WorksheetオブジェクトのSpinnersメソッド等を確認できるようになります。

Spinners.Addメソッド

Worksheetオブジェクトに用意されているSpineersメソッドでSpinnersコレクションを取得し、

With ActiveSheet.Spinners.Add(rng.Left, rng.Top, 15, rng.Height)

SpinnersコレクションのAddメソッドを使って、

With ActiveSheet.Spinners.Add(rng.Left, rng.Top, 15, rng.Height)

Spinnerオブジェクト（スピンボタン）を挿入しています。

Spinners.Addメソッドの引数には、オブジェクトブラウザー詳細ペインに「Function Add(Left As Double, Top As Double, Width As Double, Height As Double)」と表示されているとおり、位置（Left・Top）と大きさ（Width・Height）を指定する必要があります。

上記のマクロではリンクするA1セルの右隣のB1セルにピッタリ収めるために、事前にB1セルを表すRangeオブジェクトをオブジェクト変数rngに代入しておいて、

Dim rng As Range
Set rng = Range("B1")

挿入するスピンボタンの左端位置を指定するSpinners.Addメソッドの第1引数Leftに、B2セルの左端位置を示すRange.Leftを、

With ActiveSheet.Spinners.Add(rng.Left, rng.Top, 15, rng.Height)

上端位置を指定する第2引数Topに、B2セルの左端位置を示すRange.Topを、

With ActiveSheet.Spinners.Add(rng.Left, rng.Top, 15, rng.Height)

スピンボタンの幅を指定する第3引数Widthには、直値15を、

With ActiveSheet.Spinners.Add(rng.Left, rng.Top, 15, rng.Height)

高さを指定する第4引数Heightに、B2セルの高さを示すRange.Heightを、

With ActiveSheet.Spinners.Add(rng.Left, rng.Top, 15, rng.Height)

それぞれ指定しています。

Spinnersの設定

Spinners.Addメソッドの戻り値は、挿入されたばかりのスピンボタンを表すSpinnerオブジェクトです。

上記のマクロでは、With文の中で、Spinnerオブジェクトのプロパティを使って、設定を行っています。

With ActiveSheet.Spinners.Add(rng.Left, rng.Top, 15, rng.Height)
　.Value = 1
　.Min = 1
　.Max = 12
　.LinkedCell = rng.Offset(0, -1).Address

Spinner.LinkedCellプロパティには、

　.LinkedCell = rng.Offset(0, -1).Address

リンクするセルの番地を文字列で指定する必要があり、

Range.Offsetプロパティで取得したA1セルを表すRangeオブジェクトのAddressプロパティを指定しています。

　.LinkedCell = rng.Offset(0, -1).Address

• 『理解するExcel VBA／図形操作の基本』を出しました。
• 『図形操作の基本』に登場する図形関連オブジェクトごとのメソッド一覧
• 『図形操作の基本』に登場する図形関連オブジェクトごとのプロパティ一覧

理解するExcel VBA／図形操作の基本

伊藤潔人 2021-11-28

Amazon