Thanks Chip - I went out and read your Event articles on your website. But
didn't see any of this covered.

Some more information and a couple of questions.

1. I have control toolbar frames on my worksheet that I've added events for
in a sheet module. Each frame consists of a textbox and spinbutton control.
I've added the events for both controls in the frame to the collection which
then gets used in a class module. All works fine.
2. In the worksheet_change event, I dynamically create a frame in a cell
offset based on the value of the currently selected cell. The frame also
consists of a textbox and spinbutton control. I can access the controls but
can't use the events in the class module to edit/control the control values.
3. Originally I thougth I could just re-initalize the collection and the
events for the newly added control would be included in the collection. But
this doesn't appear to work.
4. Will the events added using the Extensibility Library be included in the
collection so that I can use the normal class events to control both the
original and dynamic controls? If not, is there another way to do this?
5. Do I need to use coding similar to the CreateEventProc in your code for
every event I want to setup?

Thanks

Steve

P.S. After I add the dynamic control, the control will work using the class
events if I stop and restart the macro. This would make sense, since the
control is not part of the collection at runtime. Not sure if this helps...


"Chip Pearson" wrote:

I forgot the declaration for VBP. Add

Dim VBP As VBIDE.VBProject

to the existing declarations.


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




On Fri, 02 Apr 2010 11:35:30 -0500, Chip Pearson
wrote:

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
.