Steve,

First add a reference to the VBA Extensibility Library. In VBA, go to
the Tools menu, choose References, and scroll down to and check the
item "Microsoft Visual Basic For Applications Extensibility Library
5.3". Then, use code like the following:

Sub AAA()
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim L As Long
Dim Ctrl As OLEObject

Set Ctrl = Worksheets("Sheet1").OLEObjects("CommandButton1")
Set VBP = ThisWorkbook.VBProject
Set VBComp = VBP.VBComponents(Ctrl.TopLeftCell.Worksheet.CodeNa me)
Set CodeMod = VBComp.CodeModule
L = CodeMod.CreateEventProc("Click", Ctrl.Name)
CodeMod.InsertLines L + 1, " Msgbox ""Hello World"""
End Sub

This adds the following to the code module for Sheet1

Private Sub CommandButton1_Click()
MsgBox "Hello World"
End Sub


For lots more information about creating and working with VBA objects
via code, see www.cpearson.com/Excel/VBE.aspx .

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com






On Fri, 2 Apr 2010 09:00:01 -0700, sarndt
wrote:

When I dynamically add controls to a worksheet that already has controls on
it, how do I modify the collection to include the new events?

Thanks

Steve