Add New Events to Existing Collection of Events

[sarndt]

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

 

[Chip Pearson]

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.CodeName)
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

 

[Chip Pearson]

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

 

[sarndt]

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...

 

[sarndt]

One more question - If I still need to create each event in the collection
individually, I assume this code will only be needed/used when the object is
created. And that the next time, the macro is run on worksheet
startup/initialization, the object will be referenced as a design-time object
- not a run-time object.

Thanks

Steve

 

[Chip Pearson]

You are probably raising some confusion by thinking of events as
belonging to some sort of collection to and from which events are
added and removed, or that can be somehow reset or refreshed. Events
don't really make up any kind of collection. If a procedure with the
correct name exists in one of several places that Excel looks for
event code, Excel executes the code.

You can't get all the events from a TextBox when it is referenced
tangentially in another object module. A TextBox is a bit of an
oddball in this respect. Almost all the other controls expose all
their events when instantiated in another object module. TextBox does,
and I have no idea why that is the case.

Creating code via the Extensibility library is the same as if you
typed in the code manually. When you save the workbook, the newly
created event procedures will be saved. Therefore, you should
probably check whether the event proc already exists before creating a
new one. Otherwise, you'll get compiler errors.

Broadly speaking, I recommend against using code to modify its own
VBProject, especially if that code is to modify its own VBComponent.
Sometime, the new code will not be available and recognized until VBA
decides to recompile the code. Sometimes you can force it with

Application.VBE.Command Bars.FindControl(ID:=578)
If .Enabled = True Then
.Execute
.Execute ' yes, call it twice
End If
End With

but this is not a foolproof method.

You might be better off manually creating the event procedures before
hand and then adding controls dynamically at run time. It causes no
error and no problems if you have event code for an object that
doesn't yet exist.

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

 

[sarndt]

You're right - what I was thinking is wrong. Maybe, I'm trying to overkill
this since I can't get it to work...Here's what I got so far:

1. I have a worksheet that has control toolbox frames on it - each with a
textbox and spinbutton control in the frame. When the worksheet is
activated, it calls the InitalizeEvents sub of the sheet module. For example:

For Each objcontrol In ActiveSheet.OLEObjects

If TypeName(objcontrol.Object) = "Frame" Then
Set clsEvents = New CTimeControl
Set clsEvents.TBControl = objcontrol.Object.Controls(0) - textbox
Set clsEvents.SBControl = objcontrol.Object.Controls(1) -
spinbutton
mcolEvents.Add clsEvents
End If
Next

In the class module, I have:
Private WithEvents TB As MSForms.TextBox
Private WithEvents SB As MSForms.SpinButton

Public Property Set TBControl(objNewTB As MSForms.TextBox)
Set TB = objNewTB
End Property
Public Property Set SBControl(objNewSB As MSForms.SpinButton)
Set SB = objNewSB
End Property

This all works fine and I'm able to work with all of the events of all of
the controls that are designed on the worksheet using TB and SB appropriately.

In the Worksheet_Change event, when the user picks a paticular value from
the list, I want to dynamically add a frame with the same controls and then
ue the TB and SB events to do the same type of updating and editing. For
example:

Set objframe =
ActiveSheet.OLEObjects.Add(classtype:="Forms.Frame.1").Object
Set objTextBox = objframe.Controls.Add("Forms.Textbox.1")
Set objSpinButton = objframe.Controls.Add("Forms.Spinbutton.1")
objframe.Activate
With objframe

This adds the frame and controls and I can access them. But the events
don't work. What I'm struggling to figure out is how to get the TB and SB
events to work with the dynamic control.

Steve