【VBA】シート上のフォームコントロールを操作する方法 (ControlFormat オブジェクト)

目次

はじめに

Excelのワークシートには、「フォームコントロール」と「ActiveXコントロール」という2種類のコントロールを配置できます。このうち、より手軽に使えるのが「フォームコントロール」です。

通常、これらのコントロールの値は、LinkedCell(リンクされたセル)プロパティを使ってセルと連動させますが、VBAを使えば、セルを介さずにコントロールの状態を直接読み書きすることが可能です。

この記事では、VBAでワークシート上のフォームコントロール(チェックボックス、リストボックス、スピンボタンなど)を操作するための鍵となる ControlFormat オブジェクトと、その主要なプロパティについて解説します。


ControlFormat オブジェクトへのアクセス方法

シート上のフォームコントロールは、VBAからは Shape(図形)オブジェクトとして認識されます。その Shape オブジェクトの .ControlFormat プロパティを通じて、コントロール特有の機能にアクセスします。

基本構文: ActiveSheet.Shapes("コントロール名").ControlFormat


コントロール別の主要プロパティとサンプルコード

1. チェックボックス / オプションボタン

チェックボックスやオプションボタンの状態を取得・設定します。

主要プロパティ:

  • .Value: コントロールの状態を取得・設定します。
    • チェックボックス: オン = 1 (xlOn), オフ = -4146 (xlOff)
    • オプションボタン: 選択 = 1 (xlOn), 非選択 = -4146 (xlOff)
  • .LinkedCell: リンクするセルのアドレスを文字列で設定します (例: "A1")。

サンプルコード

' チェックボックスの状態を操作する
Sub ManipulateCheckBox()
    Dim chkBoxShape As Shape
    Set chkBoxShape = ActiveSheet.Shapes("ApprovalCheckBox")
    
    ' チェックボックスをオンにする
    chkBoxShape.ControlFormat.Value = xlOn
    
    ' 現在の状態をメッセージボックスに表示
    If chkBoxShape.ControlFormat.Value = xlOn Then
        MsgBox chkBoxShape.Name & " はオンです。"
    Else
        MsgBox chkBoxShape.Name & " はオフです。"
    End If
End Sub

2. リストボックス / コンボボックス

リストの項目や、どの項目が選択されているかを操作します。

主要プロパティ:

  • .ListFillRange: リスト項目の元となるセル範囲を文字列で設定します (例: "Sheet2!A1:A10")。
  • .ListIndex: 選択されている項目のインデックス番号(1始まり)を取得・設定します。何も選択されていない場合は 0 を返します。
  • .Value: .ListIndex と同じく、選択されている項目のインデックス番号を返します。
  • .LinkedCell: 選択結果(インデックス番号)を書き出すセルのアドレスを設定します。

サンプルコード

' リストボックスを操作する
Sub ManipulateListBox()
    Dim listBoxShape As Shape
    Set listBoxShape = ActiveSheet.Shapes("ProductListBox")
    
    With listBoxShape.ControlFormat
        ' リストの項目範囲を設定
        .ListFillRange = "ItemList!A2:A6"
        
        ' リストの3番目の項目を選択状態にする
        .ListIndex = 3
        
        ' 選択されている項目のインデックスを表示
        MsgBox .ListIndex & "番目の項目が選択されています。"
    End With
End Sub

3. スピンボタン / スクロールバー

数値の範囲や現在の値を操作します。

主要プロパティ:

  • .Value: 現在の値を取得・設定します。
  • .Min: 設定可能な最小値。
  • .Max: 設定可能な最大値。
  • .SmallChange: ボタンの矢印を1回クリックしたときの値の変化量。
  • .LinkedCell: 現在値を書き出すセルのアドレスを設定します。

サンプルコード

' スピンボタンを操作する
Sub ManipulateSpinner()
    Dim spinnerShape As Shape
    Set spinnerShape = ActiveSheet.Shapes("QuantitySpinner")
    
    With spinnerShape.ControlFormat
        ' 範囲を設定
        .Min = 1
        .Max = 100
        
        ' 現在値を設定
        .Value = 10
        
        ' リンクするセルを設定
        .LinkedCell = "C5"
        
        MsgBox "現在の値: " & .Value
    End With
End Sub

まとめ

今回は、VBAの ControlFormat オブジェクトを使って、シート上のフォームコントロールを直接操作する方法を解説しました。

  • シート上のコントロールは Shapes コレクションでアクセスする。
  • .ControlFormat プロパティを介して、コントロール特有の機能に触れる。
  • .Value, .ListFillRange, .LinkedCell などのプロパティで、状態の読み書きが可能。

LinkedCell を使ってセルと連動させるのがフォームコントロールの基本的な使い方ですが、VBAで直接値を操作できることを知っておくと、より動的で高度なシートアプリケーションを作成する際に非常に役立ちます。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

私が勉強したこと、実践したこと、してることを書いているブログです。
主に資産運用について書いていたのですが、
最近はプログラミングに興味があるので、今はそればっかりです。

目次