任意の数だけ自動生成されたOLEオブジェクトのチェックボックスを連動させるためにクラスモジュールを使いたい

総合スコア33715

2020/09/08 12:37

ありがとうございます。Chk1、2に、OLEオブジェクトのチェックボックスを入れるようにして、さらにそれは、クリックしたときのプロシージャの名前の文字列としても扱えるということでしょうか？いただいたコードをコピペしてみたのですが、チェックボックスは連動しておらず、デバッグでは「Sub または Function が定義されていません。」というエラーが出ます。何かプロシージャを呼び出しているわけでもないし、クラスモジュールの名前もlsLinkedCheckBoxesに変えているし、何が考えられるでしょうか？よろしくお願いいたします。

2020/09/08 12:50

「クリックしたときのプロシージャの名前の文字列としても扱えるということでしょうか？」というが何を意味しているのかわかりません。たぶんクラスモジュールの機能を誤解しているように思います。プロシージャの名前の文字列として扱う必要はなにもないです。提示のコードだけで機能するはずです。 > クラスモジュールの名前もlsLinkedCheckBoxesに変えているし、正しくは clsLinkedCheckBoxes ですけど、間違えてませんか。コピペミスならいいですが。 > 「Sub または Function が定義されていません。」というエラーが出ます。そのとき、どの行が反転表示されますか。

2020/09/08 23:35

ありがとうございます。クラスモジュールを勉強して、イメージしていたものは Private Sub CheckBox45_Click()　の45の部分を45から88まで連番で作りたい CheckBox45_Clickは、プロシージャの名前で文字列のはずなので文字列を操作すればいいはずという感じで、クラスモジュールを使って文字列を操作してプロシージャ名に格納する方法が、検索しても見つけられないではまっていた感じです。すみません、コピペミスです。clsLinkedCheckBoxes にできています。 Private Sub Workbook_Open()の行をブレイクポイントにすると 4行下の、Chkes(i).SetCtrl のChkesが反転されます。チェックボックスのあるシートも、Sheet1です。よろしくお願いいたします。

2020/09/09 04:56 編集

ThisWorkbookのモジュールの先頭に Private Chkes(1 To 44) As New clsLinkedCheckBoxes は記述してありますか。また、メニューの[デバッグ]-[VBAProjectのコンパイル]をクリックして、他にコンパイルエラーがでないか確認してください。エラーがでないとこを確認したら、[ファイル]-[ブック名の上書き保存]をクリックしてください。その後、ブックをいったん閉じて再度開いてください。するとチェックボックスは連動しているはずです。クラスモジュールに関しては間違って理解しているようなので、回答にクラスモジュールの仕組みを簡単に解説しておきます（執筆中ですのでしぱらくお待ちください）。

2020/09/09 04:57

クラスモジュールについて解説を追加しましたので、参照してください。

2020/09/10 06:06

ありがとうございます。わかりやすい説明に感激しています。クリック時に動くようにするには　プロシージャの名前を文字列で作るのではなく、クラスモジュールのWith Eventsで指定しているんですね。おっしゃる通りモジュールの先頭に置いていませんでした。直したら正しく動いてくれました。このチェックボックスは、作業ごとに作り入力して時間割を作成した後は捨ててしまってもいいものなので、チェックボックス生成時に関連付けもしてしまいたいのですが、今のところうまくいっていません。もう少し自力で考えてみてわからなかったらまた質問しようと思います。

2020/09/10 06:55

下記の私の回答が参考になると思います。 VBA - コード中で生成されたコマンドボタンの処理の指定｜teratail https://teratail.com/questions/288841#reply-409488 上記はユーザーフォーム上にコマンドボタンとラベルのセットを複数生成するというものですので、すこし違いますが、生成時に関連付けするところとか参考になると思います。。今回の回答はコントロールの数が固定ということで配列にクラスを格納しましたが、もし、コントロール数がその時々で変わるということならCollectionオプジェクトに格納すると個数の可変に対応できますので、Collectionの使い方も調べるといいでしょう。

2020/09/10 23:03 編集

処理全体がわからないので推測ですか、自動生成して配置するならシート上でなくユーザーフォームで作成した方がいいように思えます。ユーザーフォームについても検討されてみては。

2020/09/12 03:36

ありがとうございます。 Collectionオブジェクトを勉強中です。月一回の処理、列ヘッダーに日付、行ヘッダーに名前があり、月によって数も内容も変わります。（画像を追加しました。）任意の日付と名前を入力した後、日付の後ろに列を挿入する 2つのチェックボックスを生成するそのチェックボックスに任意の入力をした後チェックボックスの下の行に、行を挿入して時間割を作成するといった具合です。名前が多くなりすぎると表に埋め込んであるほうがわかりやすいかと思ってこの形にしています。ユーザーフォームでも大丈夫でしょうか？

2020/09/12 04:06

その辺は好みになりますので、シート上でもいいとは思います。ユーザーフォームで表形式らしくするなら、フレームの上にチェックボックスを配置して縦横に並べればそれらしくなります。手作業でするなら面倒ですが、コードで生成配置するなら数行コードを追加するだけです。データ数が多い場合は、ユーザーフォームにスクロールバーを表示させることもできます。私はAccessでの開発がメインですので、データはテーブルに格納、閲覧や入力はフォームでという分業制になれているのでそういう発想になりがちです。フォームの方かアプリケーションっぽくみえますし。でも、シート上できっちり作成しているシステムもありますのでこれはもうプログラマーの好みですね。

2020/09/12 04:56

ありがとうございます。ユーザーフォームも勉強してみます。わからなかったらまたお願いします。

行動規範の内容に同意します

clsBpcaを使った場合でも上記リンクのようにするには、
標準モジュールに書くのでいいでしょうか？

clsBpca は、コントロールオブジェクト変数を宣言したモジュール内に
イベントプロシジャーを記述できるようにする事が主眼のクラスです。
イベントプロシジャーを記述するので、必然的にオブジェクトモジュール
（UserForm , ThisWorkbook , Sheet1モジュール等）での利用となります。

設問を見直しましたら、「右チェックボックス」の入力可否のみで、
それもクラスモジュール内で直接処理すれば十分であり、Click/Change等の
様々なイベントプロシジャー引っ張って来る必要は無いようなので、
本件では clsBpca は不要ですね。

混乱させてしまったようで申し訳ないです。

クラスモジュールで、もう少しスッキリできないものかと、
最初に考えた案（実際は失敗でしたが・・・）

--- 標準モジュール ---
Private CheckBoxes() As clsCheckBoxes

Public Sub CreateCheckBoxGroup()
Dim j As Integer
Dim k As Integer
Dim Rng As Range

Dim RowSize As Integer
Dim ColumnSize As Integer

    'サンプルで基点[A3セル] 縦２横３の例
    RowSize = 2
    ColumnSize = 3
    
    ReDim CheckBoxes(1 To RowSize, 1 To ColumnSize)
    
    'チェックボックスの生成
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")
        For j = 1 To RowSize
            For k = 1 To ColumnSize
                Set Rng = .Range("A3").Offset(j - 1, (k - 1) * 2)    '左Boxセル
                Set CheckBoxes(j, k) = New clsCheckBoxes
                CheckBoxes(j, k).CreateCheckBox Rng     '右Boxセルはクラス内で設定
                DoEvents
            Next k
        Next j
    End With
    Application.ScreenUpdating = True
End Sub


Sub TEST()
    MsgBox "[1,1] 左=" & CheckBoxes(1, 1).LeftValue & _
                  " , 右=" & CheckBoxes(1, 1).RightValue & vbCrLf & _
           "[2,3] 左=" & CheckBoxes(2, 3).LeftValue & _
                  " , 右=" & CheckBoxes(2, 3).RightValue
End Sub

--- clsCheckBoxes クラスモジュール ---

Private WithEvents ChkLeft As MSForms.CheckBox
Private ChkRight As MSForms.CheckBox

Public Sub CreateCheckBox(ByRef RngLeft As Range)
Dim ParentWksh As Worksheet
Dim RngRight As Range

    Set ParentWksh = RngLeft.Parent
    Set RngRight = RngLeft.Offset(0, 1)
    
    '--- 左チェックボックス ---
    With ParentWksh.OLEObjects.Add _
                     (ClassType:="Forms.CheckBox.1", _
                      Link:=False, DisplayAsIcon:=False)
        .Width = RngLeft.Width - 5
        .Height = RngLeft.Height - 5
        '対象セルの中央に配置
        .Top = RngLeft.Top + (RngLeft.Height - .Height) / 2
        .Left = RngLeft.Left + (RngLeft.Width - .Width) / 2
        With .Object
            .Caption = ""
            .BackColor = vbWhite
            .Value = False
        End With
        
        Set ChkLeft = .Object
    End With
    
    '--- 右チェックボックス ---
    With ParentWksh.OLEObjects.Add _
                    (ClassType:="Forms.CheckBox.1", _
                     Link:=False, DisplayAsIcon:=False)
        .Width = RngRight.Width - 5
        .Height = RngRight.Height - 5
        '対象セルの中央に配置
        .Top = RngRight.Top + (RngRight.Height - .Height) / 2
        .Left = RngRight.Left + (RngRight.Width - .Width) / 2
        With .Object
            .Font.Size = 9
            .Caption = "ﾘﾊ"
            .ForeColor = vbBlack
            .BackColor = vbWhite
            .Value = False
            .Enabled = False
        End With
        
        Set ChkRight = .Object
    End With
End Sub

Public Property Get LeftValue() As Boolean
    LeftValue = ChkLeft.Value
End Property

Public Property Get RightValue() As Boolean
    RightValue = ChkRight.Value
End Property

'左チェックボクスで[右チェックボクス]の入力可否を設定
Private Sub ChkLeft_Click()
    If (ChkLeft.Value = False) Then
        ChkRight.Value = False
        ChkRight.Enabled = False
    Else
        ChkRight.Enabled = True
    End If
End Sub

チェックボックス作成の都度「VBAプロジェクト」がリセット
される事により、モジュールレベル変数 CheckBoxes() が
空になる為、折角セットしたクラスオブジェクトが１個も残らない
状況となり、イベントも捕れないし、値も取れない。

結局、シートではチェックボックスの作成とクラスの設定は分ける
しかないと判った（という事が既に述べられている事に早く気付けば良かった）

修正案

--- 標準モジュール ---

'サンプルで基点[A3セル] 縦２横３の例
Private Const RowSize As Integer = 2
Private Const ColumnSize As Integer = 3
Private CheckBoxes() As clsCheckBoxes

Public Sub CreateCheckBoxGroup()
Dim j As Integer
Dim k As Integer
Dim Rng As Range

    'チェックボックスの生成
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")
        For j = 1 To RowSize
            For k = 1 To ColumnSize
                Set Rng = .Range("A3").Offset(j - 1, (k - 1) * 2)  '左Boxセル
                Call CreateCheckBox(j, k, Rng)  '右Boxセルはルーチン内で設定
                DoEvents
            Next k
        Next j
    End With
    Application.ScreenUpdating = True
End Sub


Public Sub CreateCheckBox(ByVal RowIdx As Integer, ByVal ColIdx As Integer, ByRef RngLeft As Range)
Dim ParentWksh As Worksheet
Dim RngRight As Range

    Set ParentWksh = RngLeft.Parent
    Set RngRight = RngLeft.Offset(0, 1)
    
    '--- 左チェックボックス ---
    With ParentWksh.OLEObjects.Add _
                     (ClassType:="Forms.CheckBox.1", _
                      Link:=False, DisplayAsIcon:=False)
        .Width = RngLeft.Width - 5
        .Height = RngLeft.Height - 5
        '対象セルの中央に配置
        .Top = RngLeft.Top + (RngLeft.Height - .Height) / 2
        .Left = RngLeft.Left + (RngLeft.Width - .Width) / 2
        With .Object
            .Caption = ""
            .BackColor = vbWhite
            .Value = False
        End With
        
        'チェックボクスの名前を変更( CheckBoxLeft02_05 等)
        .Name = "CheckBoxLeft" & Format(RowIdx, "00") & "_" & Format(ColIdx, "00")
    End With
    
    '--- 右チェックボックス ---
    With ParentWksh.OLEObjects.Add _
                    (ClassType:="Forms.CheckBox.1", _
                     Link:=False, DisplayAsIcon:=False)
        .Width = RngRight.Width - 5
        .Height = RngRight.Height - 5
        '対象セルの中央に配置
        .Top = RngRight.Top + (RngRight.Height - .Height) / 2
        .Left = RngRight.Left + (RngRight.Width - .Width) / 2
        With .Object
            .Font.Size = 9
            .Caption = "ﾘﾊ"
            .ForeColor = vbBlack
            .BackColor = vbWhite
            .Value = False
            .Enabled = False
        End With
        
        'チェックボクスの名前を変更( CheckBoxRight02_05 等)
        .Name = "CheckBoxRight" & Format(RowIdx, "00") & "_" & Format(ColIdx, "00")
    End With
End Sub


'チェックボクスの名前は[ CheckBoxLeft02_05 , CheckBoxRight02_05 等]になっている
'数字は[縦Index_横Index ]
Public Sub ClassRegister()
Dim j As Integer
Dim k As Integer
Dim strIdx As String
Dim obj As MSForms.CheckBox
    
    ReDim CheckBoxes(1 To RowSize, 1 To ColumnSize)
    With Worksheets("Sheet1")
        For j = 1 To RowSize
            For k = 1 To ColumnSize
                Set CheckBoxes(j, k) = New clsCheckBoxes
                strIdx = Format(j, "00") & "_" & Format(k, "00")
                Set CheckBoxes(j, k).ItemLeft = .OLEObjects("CheckBoxLeft" & strIdx).Object
                Set CheckBoxes(j, k).ItemRight = .OLEObjects("CheckBoxRight" & strIdx).Object
            Next k
        Next j
    End With
End Sub


Sub TEST()
    MsgBox "[1,1] 左=" & CheckBoxes(1, 1).LeftValue & _
                  " , 右=" & CheckBoxes(1, 1).RightValue & vbCrLf & _
           "[2,3] 左=" & CheckBoxes(2, 3).LeftValue & _
                  " , 右=" & CheckBoxes(2, 3).RightValue
End Sub

--- clsCheckBoxes クラスモジュール ---

Private WithEvents ChkLeft As MSForms.CheckBox
Private ChkRight As MSForms.CheckBox


Public Property Set ItemLeft(ByRef Item As MSForms.CheckBox)
    Set ChkLeft = Item
End Property

Public Property Set ItemRight(ByRef Item As MSForms.CheckBox)
    Set ChkRight = Item
End Property

Public Property Get LeftValue() As Boolean
    LeftValue = ChkLeft.Value
End Property

Public Property Get RightValue() As Boolean
    RightValue = ChkRight.Value
End Property

'左チェックボクスで[右チェックボクス]の入力可否を設定
Private Sub ChkLeft_Click()
    If (ChkLeft.Value = False) Then
        ChkRight.Value = False
        ChkRight.Enabled = False
    Else
        ChkRight.Enabled = True
    End If
End Sub

投稿2020/10/02 16:33

総合スコア51

2020/10/03 11:57

＞clsBpca は、コントロールオブジェクト変数を宣言したモジュール内に＞イベントプロシジャーを記述できるようにする事が主眼のクラスです。ようやく理解しました。記事自体を間違った先入観で読んでしまっているので混乱していたようです。今後必要になるときもあると思うのでもう少し読み込んでみます。コード自体もほぼ理解できたと思います。ありがとうございました。

行動規範の内容に同意します

こんにちは。
「擬似からの脱却」サイトの角田です。

2020/9/7投稿という事はサンプルコード($11)に
シート上のコントロールに対する利用例を追加記載した後
ですが、気付いて頂けなかったですかね・・・

汎用クラス(clsBpca)を利用すると以下のようなマクロになります。
(補)
clsBpca はブラックボックスとして、clsBpca 自体のマクロコードを
理解(解読)する必要はありませんよ。只、利用するだけです。

VBA
1【 以下は全てThisWorkbook モジュールに記述します 】
2
3
4Private WithEvents Bpca_ShChk_Left As clsBpca
5Private WithEvents Bpca_ShChk_Right As clsBpca
6
7'---------------------------------------------------------
8Private Sub Workbook_Open()
9Dim j As Integer
10
11    Set Bpca_ShChk_Left = New clsBpca    ' インスタンスの生成
12    Set Bpca_ShChk_Right = New clsBpca
13
14    ' [ ワークシート.OLEObjects(コントロール名).Object ]のスタイルで指定します
15    With Worksheets("Sheet1")
16        For j = 1 To 44
17          Bpca_ShChk_Left.Add  .OLEObjects("CheckBox" & (j + 44)).Object
18          Bpca_ShChk_Right.Add  .OLEObjects("CheckBox" & j).Object
19
20          'この設定により左(45-88)/右(1-44)のチェックボックスの
21          'Index値は共に[1～44]となり、同じIndex値の左/右が対応する事になる。
22        Next j
23    End With
24
25    Bpca_ShChk_Left.Rgst  BPCA_Change
26    Bpca_ShChk_Right.Rgst  BPCA_Change
27End Sub
28
29'---------------------------------------------------------
30Private Sub Workbook_BeforeClose(Cancel As Boolean)
31'VBAプロジェクトのリセットにより、
32'オブジェクト変数(Bpca_ShChk_Left,Bpca_ShChk_Right)が初期化されている
33'場合(実行時エラーになる)に備えて[ On Error Resume Next ]を必ず記述する
34    On Error Resume Next
35    Bpca_ShChk_Left.Clear
36    Bpca_ShChk_Right.Clear
37    Set Bpca_ShChk_Left = Nothing
38    Set Bpca_ShChk_Right = Nothing
39End Sub
40
41'---------------------------------------------------------
42Private Sub Bpca_ShChk_Left_Change(ByVal Index As Integer)
43    If (Bpca_ShChk_Left(Index).Value = False) Then
44        Bpca_ShChk_Right(Index).Value = False
45        Bpca_ShChk_Right(Index).Enabled = False
46    Else
47        Bpca_ShChk_Right(Index).Enabled = True
48    End If
49End Sub
50

投稿2020/09/27 17:08

編集2020/09/30 10:35

総合スコア51

2020/09/29 01:43

ありがとうございます。調べる際、念頭に置いていたのが下記のイメージでした。 Private Sub CheckBox45_Click()　の45の部分を45から88まで連番で作りたい CheckBox45_Clickは、プロシージャの名前で文字列のはずなので文字列を操作すればいいはずという感じで、クラスモジュールを使って文字列を操作してプロシージャ名に格納する方法が、検索しても見つけられないという感じでした。的が外れていますが。記事を読ませていただいて、5、６ページ目までは何とかついていけたのですが、その後想定していたイメージと離れすぎて、目は通したのですが理解できていません。今夜は時間が取れそうなので試してみます。

2020/09/29 05:19

> CheckBox45_Clickは、プロシージャの名前で文字列のはずなので > 文字列を操作すればいいはずという感じで、 > クラスモジュールを使って文字列を操作してプロシージャ名に > 格納する方法が、検索しても見つけられない > という感じでした。そもそも、プロシジャー名はVBAで扱う「文字列」ではないですから。メモリ上の『アドレス』に変換されていますから。もし無理やりに行なうとすると、恐らくプロシジャの実行アドレスを指すポインタを書き換えるアセンブラコードをバイナリで記述した定数を用意して云々というような事になるのでしょうね。私は知らないけど・・・

2020/09/30 00:19 編集

昨夜コピペしてみたのですが、 Bpca_ShLbl_Left.Rgst BPCA_Change の行で変数が定義されていないと出るのと、ファイルを開いたときにワークシート上のOLEオブジェクトを取得できないとのエラーが出ます。今まで何も考えずシートモジュールにコードを書いてきました。標準モジュールとブックモジュールとの違いを少し調べてみましたが、シートモジュールに書かれていることがエラーの原因かはわかりませんでした。そもそもなぜ今回ブックモジュールに書くのかもよくわかりません。少なくとも、sheet1が作業シートなのでWith Worksheets("Sheet1")で問題ないと思うのですが。。。よろしくお願いいたします。

2020/09/30 10:56

すみません。 Workbook_Open 内の最後の > Bpca_ShLbl_Left.Rgst BPCA_Change > Bpca_ShLbl_Right.Rgst BPCA_Change は、ShLbl のところを SnChk に修正してください（スレッドは修正済）。 > ファイルを開いたときにワークシート上の > OLEオブジェクトを取得できないとのエラーが出ます。上記修正をして、Sheet1上に CheckBox1～CheckBox88 が配置されていれば、そのエラーは出ない筈です。 CheckBox1～CheckBox88 は、ちゃんと在りますか？ > そもそもなぜ今回ブックモジュールに書くのかもよくわかりません。ワークブックを開いたら、即、チェックボックスが動作するようにする為には、 WorkBook_Open で初期処理を実行して、Sheet1上のチェックボックスを clsBpcaのオブジェクト変数に取り込む必要があります。 WorkBook_Open で処理するのですから、Bpca_ShChk_Left/Bpca_ShChk_Right は WorkBook_Open がある ThisWorkbook モジュールにて定義する必要があります。補：Thisworkbook や Sheet1 モジュールなどで、Public宣言で変数定義しても、そのモジュール以外では参照できません（Public と書いても Private 扱いです）。 clsBpca オブジェクトが ThisWorkbook モジュールで定義されているので、当然、そのイベントプロシジャーも Thisworkbook モジュールで記述する事になります。