Excelで動的にイベントを作成する方法

質問

Excelのシート上へコントロールを動的に生成,配置するVBAを作っています。とりあえず生成,配置は出来たのですが、そのコントロールに対し、イベントプロシージャを動的に作成するところが上手く出来ません。

Access VBAだと、「CreateEventProc」のキーワードでヘルプを調べると出てくるサンプルコードで動くのですが、同じものをExcel VBAで動かそうとするとエラーが出ます。

Excelでは動的にイベントプロシージャを作成できないのでしょうか？もし作成する方法をご存知の方いらっしゃいましたら、ご教授下さい。宜しくお願い致します。

回答No.1

nishi6 · ベストアンサー

Access97のCreateEventProcの参考例には、フォームにコマンドボタンを貼り付け、Clickイベントを割り当てています。
同じようにExcelで作ってみました。こういう意味でした？
Sheet1に、ボタンをVBAで作って、VBAで定義したマクロをVBEに書き込んでいます。若干、画面が切り替わるかもしれません。
VBEのツール→参照設定で、 Microsoft Visual Basic Editor for Application Extensibility にチェックします。

既にあるマクロをフォームのボタンに割り当てたりする場合はOnActionで可能です。(当方、Excel97です)
（当然ですが、下のコードを2回、3回と動かすときは、作ったボタンと書きこんだコードは事前に消してください）

Sub Add_ButtonAndMacro()
　　Dim bk As Workbook 'ブック
　　Dim sht As Worksheet 'シート
　　Dim rg As Range 'セル
　　Dim obj As OLEObject 'オブジェクト
　　Dim cdMoj As CodeModule 'コードモジュール
　　Dim Ln As Long '行カウンタ

　　Application.ScreenUpdating = False
　　Set bk = ThisWorkbook 'ブック
　　Set sht = ActiveSheet 'シート
　　Set rg = sht.Range("C4:G6") 'ボタンの位置。これは例
　　With rg 'ボタンを作る
　　　　Set obj = sht.OLEObjects.Add(classtype:="Forms.CommandButton.1", _
　　　　　　　　　　　　　　　　link:=False, displayasicon:=False, _
　　　　　　　　　　　　　　　　Left:=.Left, Top:=.Top, _
　　　　　　　　　　　　　　　　Width:=.Width, Height:=.Height)
　　End With
　　obj.Object.TakeFocusOnClick = False
　　obj.Object.Caption = "VBAで書いたメッセージ呼出し"

　　'クリックイベント
　　Set cdMoj = ThisWorkbook.VBProject.VBComponents(sht.Name).CodeModule
　　Ln = cdMoj.CreateEventProc("Click", obj.Name)
　　cdMoj.InsertLines Ln + 1, "MsgBox ""ＶＢＡで追加したマクロです。"""

　　'VBE画面を閉める。
　　Application.VBE.MainWindow.Visible = False
　　Application.ScreenUpdating = True
End Sub

お礼

ありがとうございます！まさに悩んでいたポイントずばりです！MSDN見てもCreateEventProcはAccess用のメソッドだと書いてあるし、Excelじゃ動的に作成出来ないのかな、とあきらめかけていました...。本当にありがとうございます！！