EXCEL シートに配置したチェックボックスの値を取得する

2023年7月6日

ここは涼しいぞ、天国だと落ち着いた顔をしている柴犬です。

概要

excel のシートに貼り付けたチェックボックスの値を取得するのにちょっと悩みましたので記録します。

なんとか、フォームコントロール、Active X コントロールに対応できました。

このことの記述はありませんが、私が使っている Excel の教科書です。

フォームコントロール

まず、フォームコントロールを次のように配置してみます。

最初書いたコードが次のようなものです。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim sp As Shape

    For Each sp In Me.Shapes
        MsgBox sp.Name
    Next sp
End Sub

シートをダブルクリックしてみますと、きちんと拾えているようです。

次に、値を取得したいので次のコードにしてみました。

    Dim sp As Shape
    
    For Each sp In Me.Shapes
        MsgBox sp.Value
    Next sp

シートをダブルクリックしてみると

「オブジェクトは、このプロパティまたはメソッドをサポートしていません。」とエラーが出ました。

次に試みたのは、型の Shape が誤っているのかなと思い Checkbox に変更して実行してみました。

    Dim ob As CheckBox
    
    For Each ob In Me.CheckBoxes
        MsgBox ob.Value
    Next ob

フォームコントロールで作られた CheckBox の場合は次のコードで上手くいき、チェックが入っていない時 -4146 （xloff）が帰りました。

次に考えたのが、CheckBox が型 Shape であっても名前は取得できるが、値を取得できない。

この現象をどのように解釈すればいいのか考えてみました。

本来型 Object が有しているメソッド・プロパティが型 Shape で取得することによって、Shape がインターフェイスとなり隠されてしまったのではないかと考えました。

名前からオブジェクトを取得できるので、次のコードを

        Set ob = Me.CheckBoxes(sp.Name)

挿入してダブルクリックしてみます。

次のコードでチェックが入っていない時 -4146 （xloff）が返りました。

    Dim sp As Shape
    Dim ob As Object

    For Each sp In Me.Shapes
        Set ob = Me.CheckBoxes(sp.Name)
        MsgBox ob.Value
    Next sp

Active X コントールに拡張

Active X コントールを追加して次のように配置します。

次のコードで、Active X コントールの名前も取得できます。

    Dim sp As Shape

    For Each sp In Me.Shapes
        MsgBox sp.name
    Next sp

ですので、探査する Shape からタイプ（フォームコントロール、Active X コントール）が判れば場合分けにより処理を書くことができます。

名前からActive X コントールのオブジェクトが取得できます。

次のように場合分けができます。

    For Each sp In Me.Shapes
        Select Case sp.Type
        Case msoOLEControlObject
            'Active X コントール処理
        Case msoFormControl
            'フォームコントロール処理
        End Select
    Next sp

これを進めて整理したのが次のコードです。

整理したコード

copy

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
                                        Cancel As Boolean)
    Dim sp As Shape
    Dim ob As Object
    
    Cancel = True

    For Each sp In Me.Shapes
        Select Case sp.Type
        Case msoOLEControlObject
            Set ob = Me.OLEObjects(sp.Name)
            If ob.progID = "Forms.CheckBox.1" Then
                MsgBox ob.Object.Value
            End If
        Case msoFormControl
            Set ob = Me.CheckBoxes(sp.Name)
            MsgBox ob.Value = xlOn
        End Select
    Next sp

End Sub

次のように変更してダブルクリックしてみます。

MsgBox ob.Object.Value を

MsgBox sp.Name & ": " & ob.Object.Value　に変更

MsgBox ob.Value = xlOn　を

MsgBox sp.Name & ": " & (ob.Value = xlOn)　に変更

きちんと値を取得できています。

本日はここまでとします。