Excel VBA 動的に作成したチェックボックスにイベントを追加したい

質問

http://oshiete1.goo.ne.jp/qa504573.html
を参考にしたのですがエラーが出ます。

------標準モジュール-------
Dim NewChkBox As New Class1
Sub main()
Dim oObj As OLEObject

Set oObj = ActiveSheet.OLEObjects.Add(classtype:="forms.checkbox.1")

oObj.Top = 10
oObj.Left = 10
NewChkBox.cb = oObj
Set oObj = Nothing
End Sub

---------クラスモジュール--------
Public WithEvents cb As Checkbox
Private Sub cb_Click()
MsgBox "You clicked the checkbox."
End Sub

実行すると、
「オブジェクトはオートメーションイベントを発生させることができません。」
というエラーメッセージが出ます。
OLEObjectで作成したコントロールに
イベントを追加するにはどのようにしたら良いのでしょうか？

回答No.4

Wendy02 · ベストアンサー

こんにちは。

うーん、正直言って、参りましたね。

私は、yonePさんのご質問を、基本的に誤解していたかもしれません。
あくまでも、クラスのインスタンスを設けるVBAの勉強の一環としての話だと思って書いてきたのですが、実務的な内容になると、かなり違ってきます。

>　　With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
>　　　.InsertLines 　.CountOfLines + 1, strVBA
>　　End With

これは、私は、禁じられたマクロ・コードだと思っています。掲示板のマニアの間では、得意として書く人がいますが、私は、知ってはいても、掲示板ではそのようなコードを書きません。これは、お勧めしません。

そのコードを持ち出されると、こちらも、実際のマクロを出さざるを得なくなります。

私がここで書いた趣旨は、コントロールツールやクラス・イベントに対する考え方であって、実際に、それを実用で使うための目的のコードではありません。Mougのサンプルにも似たようなものがあるのかもしれませんが、私は、そのコードの実用に対しては否定的に見ています。理由は、Office VBAを扱っているので、それは、Visual Basic のように実現しようとしても、そこには制限があります。また、代わりのものがある場合は、そちらを使うのが良いと思うのです。

だから、基本的にコントロール・ツールの設計やプロパティの設定とそのコントロールに対するイベント・マクロとは別物のステージで行うものだと思っています。

これは、現在の流行とは多少のズレがあるのかもしれませんし、今後、オブジェクト指向を導入したコードとは違ってくるのは当然ですが、現在のところでは、コントロールツールで貼り付けたコードのままでのイベント付けというのは、基本的にはしません。

そこで、以下のようなコードを使うのが一般的だと私は思っています。
二種類のものを書いておきます。

(一般的だと思わない、という人がいらっしゃるかもしれませんが、それに対しては、一応静観します。ただ、VBAのコントロール配列を持たないコントロールに、私は、あまり奇をてらったコードというのは好きではありません。ここら辺は、Office VBAやマクロ全体の問題だと思うのです。----何を言っているのかさっぱり分からないというなら、この部分はムシしてください。）

コントロールツールで、イベントを設ける場合は、=== の下のコードのように書きます。当然ですが、CheckBox は、概ね、一回きりの貼り付けだと思います。しかし、クラス自体は、常に使わなくてはならないので、Auto_Open イベントの設定が必要です。フォームツール側には、それが必要ありません。

'-----------------------------------------------
'標準モジュール(一般的-ただし、OnAction の内容については要工夫）

Sub AddChkBxes()
'フォームツールをシートに置く
Dim c As Range
　For Each c In Selection.Columns(1).Cells
　　With ActiveSheet.CheckBoxes.Add(c.Left + Int(c.Width / 2), c.Top, c.Width, c.Height)
　　　　　 .Name = "CheckBox　" & .Index
　　　　　 .Caption = ""　 ' "CheckBox " & .Index '名前を取る場合
　　　　　 .OnAction = "myMsg"
　　End With
　Next c
End Sub
Sub myMsg()
'OnAction での起動
Dim chkbk As Variant
Dim myChkBox As Object
Dim i As Integer
　chkbk = Application.Caller
　If Not IsError(chkbk) Then
　i = ActiveSheet.Shapes(chkbk).OLEFormat.Object.Index
　Set myChkBox = ActiveSheet.CheckBoxes(i)

　MsgBox "You clicked Form Tool of '" & chkbk & "'" & vbCr & _
　　　　 "Value :" & CStr(myChkBox.Value > 0)
　End If
End Sub

'======================================================
標準モジュール

Public clsChkBoxes() As New Class1
Public colChkBoxes As Collection
Sub Main()
'コントロールツールのイベント付け
Dim c As Range
For Each c In Selection
　ActiveSheet.OLEObjects.Add _
　　　　　　　　　　 ClassType:="Forms.CheckBox.1", _
　　　　　　　　　　 Link:=False, _
　　　　　　　　　　 Top:=c.Top + 2, _
　　　　　　　　　　 Left:=c.Left + Int(c.Width / 2), _
　　　　　　　　　　 Height:=10, _
　　　　　　　　　　 Width:=10
　Next c
　Application.OnTime Now, "SetClass"
End Sub

Sub SetClass()
Dim cntl As Object
Dim colChkBoxes As New Collection
Dim i As Integer
　On Error Resume Next
　For Each cntl In ActiveSheet.OLEObjects
　　If TypeOf cntl.Object Is MSForms.CheckBox Then
　　　Err.Clear
　　　colChkBoxes.Add cntl.Object
　　End If
　Next cntl
　For i = 0 To colChkBoxes.Count - 1
　　　ReDim Preserve clsChkBoxes(i)
　　　Set clsChkBoxes(i) = New Class1
　　　clsChkBoxes(i).cb = colChkBoxes(i + 1)
　Next i
　On Error GoTo 0
End Sub

Sub Auto_Open()
　Call SetClass
End Sub

'--------------------------------------
'Class1 モジュール

Private WithEvents ChkBx As MSForms.CheckBox
Public Property Get cb() As MSForms.CheckBox
　　　　Set cb = ChkBx
End Property
Public Property Let cb(ByVal myNewCb As MSForms.CheckBox)
　　　　Set ChkBx = myNewCb
End Property

Private Sub Chkbx_Click()
　MsgBox "You just clicked Control Tool of " & ChkBx.Caption & vbCr & _
　　　　　"Value： " & ChkBx.Value
End Sub

補足

サンプルですが、両方とも動作を確認しました。
質問以外の部分でもいろいろと勉強になりました。
ありがとうございました。

今回の質問の意図はWendy02さんのお考えの通りで、
「動的に確保したOLEObjectのイベント追加をClassを使って実現したい」でした。

ただ、一応完成したものを作らなければならなかったので、
最後までClassで記述する方向であがいてみて、駄目なら
仕様を見直して妥協するか、あのサンプルを
利用して書こうかと思っていました。

>これは、私は、禁じられたマクロ・コードだと思っています。
正直私も、あまり使いたくないコードです。

サンプルでわからなかった部分があります。
Application.OnTime Now, "SetClass"
この行の働きです。普通にSetClassをコールすると動かないのですが、
（これが再三言われているオブジェクトが流れてしまうという現象なのでしょうが）
サンプルのように記述すると動くのが理解できませんでした。

一方Auto_Open内ではSetClassを直で呼んでいますが、これでイベントが
正常に登録されますが、これはコントロールのメモリ上の取り扱いの違いからくるものだろうと
いうことはなんとなく理解できます。

それと、SetClass内での
On Error Resume Next
ですが、これはどんな場合にエラーを拾うと想定されているのでしょうか？

回答No.6

Wendy02

こんにちは。

>それから、
>>「動的に確保したOLEObjectのイベント追加をClassを使って実現したい」でした。

後々考えてみましたが、昨今のソフトウェアとしての考え方は、一旦、設定した後は、OnTime を使わずに、ユーザーに、再起動させて、Auto_Open で、Class 設定になるのではないでしょうか。それぐらいは、許されているように思います。そういうコードなら、ノーマルだと思います。ただ、全体的な設計の問題ですが、これらのProject の内容は、ユーザーが無理に開けるのは容認しますが、OnTime にしても、Auto_Openにしても、Project は、ロックします。Class のSetting は、私自身の使っているものにも、ロックさせてあります。Class　オブジェクトは壊れることはないけれども、そのマクロ自体をいじると、オブジェクトを失います。SetClass をすれば戻りますが、あまり、そうしたことをユーザーに期待しません。また、そのたびに、クレーム処理もしたくありません。

回答No.5

Wendy02

こんばんは。

Application.OnTime Now, "SetClass"
>この行の働きです。普通にSetClassをコールすると動かないのですが、
>（これが再三言われているオブジェクトが流れてしまうという現象なのでしょうが）

これによく似た技法が、他のマクロにも出てきます。

たぶん、こういうところは、Microsoft Office の方針が揺れ動いている匂いがします。少し、仕様自体がヘンです。通常の掲示板の回答は、「それは出来ません」ということになります。

一旦、オブジェクトを作った後に、そのままの流れでは、設定できません。仮に、サブルーチン・コールしても、設定できません。これは、一種のプロテクト「コントロールツールの編集可能がオン状態」が掛かってしまうようです。もしかしたら、バージョンによって違う可能性もありますし、今後のバージョンでも、その部分は、また変わってくるかもしれません。UserFormには、そのプロテクトがありません。

このClass 設定の仕方も、他に方法(コントロールツールは、OLE-ActiveX コントロール ですから、それが、COMなどでは変わる可能性が強い）があるのかもしれませんが、一旦、流れを分断する方法しか、今の私には見つかりませんでした。

>一方Auto_Open内ではSetClassを直で呼んでいますが、これでイベントが正常に登録されますが、

既存のものに対しては、正常に登録可能です。なお、Auto_Openを設けないと、そのままアプリケーションを閉じてたら、イベントは流れてしまいます。それと、いろんなMicrosoft(MSDN) の古い資料は、Webサイトから消えてしまってきているようで、イベントの種類自体も、古いスタイルのものは、分からなくなってきているものがいくつかあります。 今後、どうなっていくのか、分かりませんし、知らないもの分からないものは、知らないままにしておけば、また必ず新たな方法が生まれると思います。VBにあった、コントロール配列は廃止になっても、VB.Net には、別のテクニックがあります。Excel VBA では、フォームツールが残っています。

>On Error Resume Next
>ですが、これはどんな場合にエラーを拾うと想定されているのでしょうか？

>For Each cntl In ActiveSheet.OLEObjects
>　　If TypeOf cntl.Object Is MSForms.CheckBox Then
>　　　Err.Clear
>　　　colChkBoxes.Add cntl.Object
>　　End If
>　Next cntl

○　If TypeOf cntl.Object Is MSForms.CheckBox Then

この行のコードの「cntl.Object」には無理があるような気がします。cntl のプロパティに Object があると決め付けてよいか、疑問です。かといって、cntl は、OLEObject ですから、タイプを選別できません。ただ、もう少し、長く使ってみないと、もしかしたら、
　If Not cntl.Object Is Nothing Then
などが必要なのかもしれません。Err.Clear は、その後の状況を見るためです。
Err.Clear はなくても、関係ありません。Collection の Add メソッドは、配列とは違いますから、エラーが出れば、インデックスが、加算されませんし、オブジェクトが追加できたら、自動的に加算されるだけです。

ただ、その部分は、暫定的です。実際の場では、コントロールツールのチェックボックスだけ、とは限らないです。それを想定しています。

だから、On Error Resume Next は、一応、置いて間違いないと思っています。

なお、Mougの参考資料のURLを書こうと思いましたが、Moug 側の内容が大幅に変わりました。同様のコードを探そうしましたが、思ったものが出てきませんでした。それから、ここまで来ると、次のVB.Net のある程度の次の技術は習得しておいたほうが無難な気がします。すべてが似ていて非なるものっていうのを、改めて知ることになると思いますが。

それから、
>「動的に確保したOLEObjectのイベント追加をClassを使って実現したい」でした。

どういうご事情かは分かりませんが、そこまでの要求は、実務上では、決められた時間内に完成するのは無理です。アルゴリズムを使ったものは、そのアルゴリズムが分かれば解決できるものですが、仕様の問題で、出来ないといっても、間違いないと思います。

今回は、お詫びしなければならないとは思いますが、私自身は知ってはいても、仕様に根ざしたものですから、回答としては、あまりすべてを書くつもりがなかったということなのです。

私は、掲示板では、いつも、相手の力量や様子を見ながら、自分の力の6～7割の回答でとどめています。一回きりの自己満足の解答なら、書き捨てで書けるのですが、いくつかの回答を書いて受け答えをしている関係で、そうしないと、突っ込まれたときに、自分の技術力を超えたりすると、逃げるに逃げられない袋小路に入ってしまうからなのです。今回の内容は、もう、オブジェクト指向の領域に入っています。しかし、今、自分のVBAを中心とした技術に行き詰まりを感じていますので、新たの勉強をしていかなくてはならないように思っています。もう、新しいことを覚えるというほどには、若くはありませんが。

お礼

ご回答ありがとうございます。

>どういうご事情かは分かりませんが、そこまでの要求は、実務上では、決められた時間内に完成するのは無理です。

そうですね。仕様変更で対応できる部分でもあるので、
もう一度検討してみることにします。

動的に部品を生成しないのであれば、
クラスを使ってイベントを問題なく追加することが
できることがわかっただけでも、収穫大です。

VB.NETへ移行できればいろいろと手がありそうな感じはするのですが、
汎用性の高さからか、まだまだEXCELでデータを欲しがる人が多く、
VBAからは当分離れられそうもないです。

この質問はこれで締めようと思います。
おつきあいありがとうございました。

回答No.3

Wendy02

こんにちは。
#2の回答者です。

>「オブジェクト変数またはwithブロック変数が設定されていません」
このエラーに関しては、

Class1
Public WithEvents cb As MsForms.CheckBox
------------------------
Set oObj = ActiveSheet.OLEObjects.Add(classtype:="forms.checkbox.1")
　　　↑
　　oObj は、OlEObject で、ここには、直接のイベントがつけられません。

NewChkBox.cb = oObj
　　　　　↑
　　　　　cb は、MsForms.CheckBox　であって、oObj ではなく、その下位プロパティのoObj.Object です。だから、それを変更すれば、エラー自体は、発生しなくなるはずです。

しかし、「一旦確保したオブジェクトは流れてしまう」は、発生しているようです。

△以下のコードではうまくいきません。

Public NewChkBox As New Class1
'---------------------------
Sub main()
　　Dim oObj As OLEObject
　　On Error GoTo ErrHandler
　　Set oObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Checkbox.1")
　　oObj.Top = 50
　　oObj.Left = 50
'-----------------------------
'　本来は、ここで分離しなくてはなりません。(以下参照）
　　Set NewChkBox.cb = oObj.Object
ErrHandler:
　'一応、念のためにエラーチェック
　If Err.Number > 0 Then
　　MsgBox "Err:" & Err.Number & " : " & Err.Description
　End If
End Sub

=====================================
'分離する(ただし、このコードでは、オブジェクト1個だけのイベント付け）
'仮に、ループにしても、最後の1個だけです。
Sub SubMain()
Dim oObj As OLEObject
　On Error Resume Next
　Set oObj = ActiveSheet.OLEObjects("CheckBox1")
　Set NewChkBox.cb = oObj.Object
End Sub

お礼

Wendy02さん、回答をありがとうございます。
あれからいろいろとネットからサンプルを探してまわったのですが、
結局Classでイベントを追加するものは見つけることが出来ませんでした。

代わりにコードをいじってイベントを追加するサンプルを見つけました。

セレクションの各セルにチェックボックスを作成してイベントを追加します。
-----------------------------------
Sub AddCheckBox()

　Dim cel As Range
　Dim ctl As OLEObject
　Dim strVBA As String

　Application.EnableEvents = False

　　For Each cel In Selection
　　　Set ctl = ActiveSheet.OLEObjects.Add(classtype:="Forms.checkbox.1", _
　　　Link:=False, Top:=cel.Top + 2, Left:=cel.Left + 2, Height:=10, Width:=10)

　　　strVBA = strVBA & vbCrLf & "Private Sub " & ctl.Name & "_Click()" & Chr(13)
　　　strVBA = strVBA & " MsgBox """ & ctl.Name & "がクリックされました""" & Chr(13)
　　　strVBA = strVBA & "End Sub"

　　Next cel

　　With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
　　　.InsertLines 　.CountOfLines + 1, strVBA
　　End With

　　Application.EnableEvents = True

End Sub
-------------------------------------
この方法だとワークシート保存時に、動的に追加したイベントまで
保存されてしまうので、それを望まない場合には、追加した
イベントのCodeModuleを削除する処理が新たに必要になりそうです。

回答No.2

Wendy02

こんばんは。

Set oObj = ActiveSheet.OLEObjects.Add(classtype:="forms.checkbox.1")

なら、シート上のコントロールツールのCheckBox を貼り付けるわけですね。コントロールツールのCheckBox なら、

　　Public WithEvents cb As Checkbox ではなく、

MsForms.CheckBox　だと思います。

しかし、一般的に、このコードは、すでに設定したものに対して、インスタンスを設けると思うのです。そのように、シートのオブジェクトで、Add で貼り付けたままのコード上では、メモリを確保出来ないので、一旦確保したオブジェクトは流れてしまうのではないでしようか。あえて行うなら、OnTime メソッドなどが必要ですが、そのようなコードも何か不自然です。

お礼

自分のコードをご指摘通り
CheckBox --> MsForms.CheckBox
としたのですが、
「オブジェクト変数またはwithブロック変数が設定されていません」
のエラーメッセージがでます。

私の理解不足なのですが、
>一旦確保したオブジェクトは流れてしまう
の部分に関係あるということなのでしょうか？

回答No.1

zap35

以前同じような質問に回答したものを一部変更しました
http://oshiete1.goo.ne.jp/qa3237782.html

UserForm1の上の方にCheckBoxとCommandButtonをそれぞれ１つずつ配置した状態で、UserForm1.Showを実行し、CommandButtonをクリックするとCheckBoxが追加されます。
CheckBoxのどれかをクリックするとチェックボックス名を表示するマクロサンプルです。（OFFICE2003で動作確認済み）

直接の回答にならないかもしれませんで、あくまで「ご参考まで」になります。ご質問の意図が違っていたらごめんなさい。

--------- 以下はUserformモジュールに貼り付ける ----------
Dim myCb() As Class1
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim cnt As Integer
　For Each ctrl In UserForm1.Controls
　　If TypeName(ctrl) = "CheckBox" Then
　　　cnt = cnt + 1
　　End If
　Next
　With Me.Controls.Add("Forms.CheckBox.1")
　　　.Top = cnt * 40
　　　.Left = 20
　　　.Width = 80
　　　.Caption = .Name
　End With
　Call UserForm_Initialize
End Sub

Private Sub UserForm_Initialize()
Dim cnt, i As Integer
　For Each ctrl In UserForm1.Controls
　　If TypeName(ctrl) = "CheckBox" Then
　　　cnt = cnt + 1
　　End If
　Next
　ReDim myCb(1 To cnt)
　For i = 1 To cnt
　　Set myCb(i) = New Class1
　　Set myCb(i).opt = Me.Controls("CheckBox" & CStr(i))
　Next i
End Sub

--------- 以下はClass1モジュールに貼り付ける ----------
Public WithEvents myCb As MSForms.CheckBox
Public Property Set opt(setcb As MSForms.CheckBox)
　Set myCb = setcb
End Property

Public Property Get opt() As MSForms.CheckBox

End Property

Sub myCb_Click()
　MsgBox myCb.Name & " がクリックされました"
End Sub

お礼

遅くなって申し訳ありません。当方の環境はEXCEL2000です。
サンプルコードの掲載ありがとうございました。
ユーザーフォーム上ではイベントを動的に追加することができるようになったのですが、
イベントを追加したいのはワークシート上に直接生成したOLEObjectです。
これをクラスを使ってかければと思って質問した次第なのですが、
やはり無理なのでしょうかね。