第136回.フォームコントロール
リボンの開発タブにコントロールの挿入があります。
フォームコントロールをVBAで扱う場合の解説になります。
フォームコントロールは、手動でシートに配置しセルにリンクすればVBAで特段に意識する必要もないものですが、時に動的に扱いたい場合も出てくるでしょう。
ワークシートにフォームコントロールを配置し、その情報を使う場合のマクロVBAについて解説します。
フォームコントロールを追加する
コントロールに対応するコレクション
| コントロールの種類 | コレクション名 |
| ボタン | Buttons |
| コンボ ボックス | DropDowns |
| チェック ボックス | CheckBoxes |
| スピン ボタン | Spinners |
| リスト ボックス | ListBoxes |
| オプション ボタン | OptionButtons |
| グループ ぼっくす | GroupBoxes |
| ラベル | Labels |
| スクロール バー | ScrollBars |
引数の説明
| 名前 | 必須 / オプション | データ型 | 説明 |
| Left | 必須 | Single | 文書の左上隅を基準にしてテキストボックスの左上隅の位置をポイント単位で指定します。 |
| Top | 必須 | Single | 文書の上端を基準にしてテキスト ボックスの左上隅の位置をポイント単位で指定します。 |
| Width | 必須 | Single | テキスト ボックスの幅をポイント単位で指定します。 |
| Height | 必須 | Single | テキスト ボックスの高さをポイント単位で指定します。 |
Dim sp As Shape
Set sp = ActiveSheet.Buttons.Add(0, 0, 100, 50)
フォームコントロールを削除/編集/情報取得
図形オートシェイプと同様にShapesとして扱えます
フォームコントロールの削除
ActiveSheet.Shapes(1).Delete
ActiveSheet.Shapes("ボタン 1").Delete
インデックスは数値または名称で指定します。
フォームコントロールの編集
With ActiveSheet.Shapes(1)
.Name = "名前"
.Top = 10
.Left = 10
.Width = 100
.Height = 50
.OnAction = "マクロ1"
End With
図形（Shape）関連のプロパティ、メソッド一覧
図形（Shape）を構成するオブジェクトには、以下のものがあります。Shapesコレクション…Shapeオブジェクトのコレクション Shapeオブジェクト…オートシェイプ、フリーフォーム、OLEオブジェクト、またはピクチャなど、描画レイヤーのオブジェクトを表します。
ただしフォームコントロールでは使えないプロパティ・メソッドも多くあります。チェックボックスのOnOffやコンボボックスの値を取得するには、
下記のフォームコントロールとして扱う必要があります。
下記のフォームコントロールとして扱う必要があります。
フォームコントロールとして扱う
コントロールの書式設定
フォームコントロールは、基本的にはオートシェイプとして扱えるのですが、
「コントロールの書式設定」の「コントロール」については他のオートシェイプにはない独特のものになります。
コントロールを右クリック
上図はチェックボックスの画面になります。
フォームコントロールの種類ごとに設定できるものが変わります。
恐らくWEBで検索しても目的のプロパティににたどり着くのは結構苦労すると思います。
これらを扱う時のプロパティを調べるときは「マクロの記録」を使うと良いでしょう。
ただし、マクロの記録のままではSelectが入っていて、これを単純には消し込めません。
「コントロールの書式設定」の「コントロール」については他のオートシェイプにはない独特のものになります。
フォームコントロールの種類ごとに設定できるものが変わります。
恐らくWEBで検索しても目的のプロパティににたどり着くのは結構苦労すると思います。
これらを扱う時のプロパティを調べるときは「マクロの記録」を使うと良いでしょう。
ただし、マクロの記録のままではSelectが入っていて、これを単純には消し込めません。
マクロの記録
ActiveSheet.Shapes.Range(Array("チェック 1")).Select
With Selection
.Value = xlOn
.LinkedCell = "$A$1"
.Display3DShading = False
End With
With ActiveSheet.Shapes.Range(Array("チェック 1"))
.Value = xlOn
.LinkedCell = "$A$1"
.Display3DShading = False
End With
With ActiveSheet.CheckBoxes("チェック 1")
.Value = xlOn
.LinkedCell = "$A$1"
.Display3DShading = False
End With
With ActiveSheet.CheckBoxes(1)
.Delete
End With
With ActiveSheet.CheckBoxes(1)
.Name = "名前"
.Top = 10
.Left = 10
.Width = 100
.Height = 50
.OnAction = "マクロ1"
End With
オートシェイプと共通のプロパティだけを扱うのであれば、Shapeオブジェクトとして扱えますが、
フォームコントロール独自のプロパティを扱う時は、フォームコントロールのコレクションを指定する必要があるという事です。
フォームコントロールの情報取得
フォームコントロールは、なるべくセルにリンクして使った方が良いでしょう。
セルにリンクしてあれば、VBAでは特にコントロールを意識することなく扱う事が出来ます。
しかし、セルにリンクされていない場合もあるでしょう。
以下ではコントロールから直接値を取得する方法になります。
シートのすべてのコンボボックスの値をイミディエイトに出力しています。
ListFillRangeはコンボボックスの「入力範囲の指定になります。」
.Valueで取得される値は、入力範囲内の選択位置になります。
したがって、ListFillRangeのValue番目がドロップボックスの値になります。
上記VBAの.Itemは.Cellsで書くこともできます。
セルにリンクしてあれば、VBAでは特にコントロールを意識することなく扱う事が出来ます。
しかし、セルにリンクされていない場合もあるでしょう。
以下ではコントロールから直接値を取得する方法になります。
Sub sample()
Dim ctl As Object
Dim rng As Range
For Each ctl In ActiveSheet.DropDowns
If ctl.ListFillRange <> "" Then
Set rng = Range(ctl.ListFillRange)
Debug.Print rng.Item(ctl.Value, 1)
End If
Next
End Sub
ListFillRangeはコンボボックスの「入力範囲の指定になります。」
したがって、ListFillRangeのValue番目がドロップボックスの値になります。
上記VBAの.Itemは.Cellsで書くこともできます。
フォームコントロールを特定する方法
シートにオートシェイプや各種のフォームコントロールが存在する場合、特定のコントロール（例えばチェックボックス）を特定する必要が出てくる場合もあります。
もちろん、できれば判別可能な名称を付けておくことが一番良いのですが、そうもいかない場合もあるでしょう
これを実行すると、シートにある全てのオートシェイプとフォームコントロールについての情報がイミディエイトに出力されます。
このTypeプロパティで判別できます。
この値が、msoFormControl(数値は8)がフォームコントロールになります。
このTypeはMsoShapeType列挙になります。
さらに、フォームコントロールの種別はFormControlTypeプロパティをXlFormControl列挙で判別します。
XlFormControl列挙
もちろん、できれば判別可能な名称を付けておくことが一番良いのですが、そうもいかない場合もあるでしょう
Sub sample1()
Dim sp As Shape
On Error Resume Next
For Each sp In ActiveSheet.Shapes
Debug.Print sp.Name _
; ",Type=" & sp.Type
Next
End Sub
このTypeプロパティで判別できます。
この値が、msoFormControl(数値は8)がフォームコントロールになります。
このTypeはMsoShapeType列挙になります。
さらに、フォームコントロールの種別はFormControlTypeプロパティをXlFormControl列挙で判別します。
| 名前 | 値 | 説明 |
| xlbuttoncontrol | 0 | ボタン |
| xlcheckbox | 1 | チェック ボックス |
| xldropdown | 2 | コンボ ボックス |
| xlEditBox | 3 | テキスト ボックス |
| xlgroupbox | 4 | グループ ボックス |
| xlLabel | 5 | ラベル |
| xllistbox | 6 | リスト ボックス |
| xlOptionButton | 7 | オプション ボタン |
| xlscrollbar | 8 | スクロール バー |
| xlspinner | 9 | スピン ボタン |
フォームコントロールを扱う実践的使用例
Sub sample2()
Dim sp As Shape
For Each sp In ActiveSheet.Shapes
If sp.Type = msoFormControl Then
If sp.FormControlType = xlCheckBox Then
sp.Left = sp.TopLeftCell.Left
End If
End If
Next
End Sub
これは、以下と同じになります。
Sub sample3()
Dim sp As CheckBox
For Each sp In ActiveSheet.CheckBoxes
sp.Left = sp.TopLeftCell.Left
Next
End Sub
フォームコントロールの最後に
上でも書きましたが、フォームコントロールは手動でセルにリンクしておいて使うようにした方が良いでしょう。
単純にセル値を扱うだけなら、VBAも簡単になって特に苦労することもありません。
とはいえ、どうしても動的に扱いたいことも出てくることもありますので、そんな時に思い出してください。
そのような場合の事前知識として記憶にとどめておく程度で構わないと思います。
単純にセル値を扱うだけなら、VBAも簡単になって特に苦労することもありません。
とはいえ、どうしても動的に扱いたいことも出てくることもありますので、そんな時に思い出してください。
そのような場合の事前知識として記憶にとどめておく程度で構わないと思います。
同じテーマ「VBA入門」の記事
第127回.他のブックのマクロを実行（Runメソッド）
第128回.マクロをショートカットで起動（OnKeyメソッド）
第129回.レジストリの操作（SaveSetting,GetSetting,GetAllSettings,DeleteSetting）
第130回.テーブル操作の概要（ListObject）
第131回.テーブル操作のVBAコード（ListObject）
第132回.その他のExcel機能（グループ化、重複の削除、オートフィル等）
第133回.引数の数を可変にできるパラメーター配列（ParamArray）
第134回.Errオブジェクトとユーザー定義エラー
第135回.ジャンプの選択オプション（SpecialCells）
第136回.フォームコントロール
第136回.ActiveXコントロール
新着記事NEW ・・・新着記事一覧を見る
SQL関数と演算子｜SQL入門（12月1日）
データの取得：集約集計、並べ替え（DISTINKT,GROUP BY,ORDER BY）｜SQL入門（11月30日）
データの取得：条件指定（SELECT,WHERE）｜SQL入門（11月29日）
データの挿入：バルクインサート｜SQL入門（11月28日）
データの挿入（INSERT）と全削除｜SQL入門（11月26日）
テーブル名変更と列追加（ALTER TABLE）とテーブル自動作成｜SQL入門（11月25日）
テーブルの作成/削除（CREATE TABLE,DROP TABLE）｜SQL入門（11月24日）
データベースに接続/切断｜SQL入門（11月23日）
SQLiteのインストール｜SQL入門（11月22日）
SQL入門：VBAでデータベースを使う｜エクセルの神髄（11月22日）
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
5.変数宣言のDimとデータ型｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.マクロって何？VBAって何？｜VBA入門
8.セルに文字を入れるとは（Range,Value）｜VBA入門
9.空白セルを正しく判定する方法（IsEmpty,IsError,HasFormula）｜VBA技術解説
10.ひらがな⇔カタカナの変換｜エクセル基本操作
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。