Lessons Learned

[sarndt]

A couple of things I've learned while developing an Excel Spreadsheet with
VBA Objects and Events:

1. It is possible to add and delete control toolbox objects to the worksheet
using code in the ThisWorkbook, the Active Worksheet, Module, and Class
Module and handle events for both the events added to the worksheet at design
time and added/deleted during run time. For example:

-------- Module1 --------

Option Explicit

Public mcolEvents As Collection
Dim clsevents As CEvents

Sub InitializeEvents()

Dim objcontrol As OLEObject

If mcolEvents Is Nothing Then
Set mcolEvents = New Collection
End If

'Loop through all the controls

For Each objcontrol In ActiveSheet.OLEObjects

If TypeName(objcontrol.Object) = "Frame" Then
Set clsevents = New CEvents
Set clsevents.FRControl = objcontrol.Object
Set clsevents.TBControl = objcontrol.Object.Controls(0)
Set clsevents.SBControl = objcontrol.Object.Controls(1)
mcolEvents.Add clsevents
GoSub Next_Control
End If

If TypeName(objcontrol.Object) = "CommandButton" Then
Set clsevents = New CEvents
Set clsevents.CBControl = objcontrol.Object
mcolEvents.Add clsevents
GoSub Next_Control
End If

If TypeName(objcontrol.Object) = "CheckBox" Then
Set clsevents = New CEvents
Set clsevents.CHControl = objcontrol.Object
mcolEvents.Add clsevents
GoSub Next_Control
End If

Next_Control:
Next

End Sub

Sub TerminateEvents()

' Destroy class collections to free up memory

Set mcolEvents = Nothing

End Sub

-------- Class CEvents --------

Option Explicit

Private WithEvents TB As MSForms.TextBox
Private WithEvents SB As MSForms.SpinButton
Private WithEvents CB As MSForms.CommandButton
Private WithEvents CH As MSForms.CheckBox
Private WithEvents FR As MSForms.Frame

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
Public Property Set CBControl(objNewCB As MSForms.CommandButton)
Set CB = objNewCB
End Property
Public Property Set CHControl(objNewCH As MSForms.CheckBox)
Set CH = objNewCH
End Property
Public Property Set FRControl(objNewFr As MSForms.Frame)
Set FR = objNewFr
End Property

Private Sub Class_Terminate()

Set TB = Nothing
Set SB = Nothing
Set CB = Nothing
Set CH = Nothing
Set FR = Nothing

End Sub

---------------------

will setup the events for frame objects containing textbox and spinbuttons,
command buttons and checkboxes.

If you need to add/delete the object from a workbook event, worksheet event,
and during code, you just need to rerun the InitializeEvents procedure in the
module using the following code whenever you add/delete an object to
recognize the events/clean-up the events:

Set mcolEvents = Nothing
Application.OnTime Now, "InitializeEvents"

This will reset the events for the objects that are still on the worksheet
after an object is either added or deleted from the screen. When you add an
object, make sure you activate or setfocus to the object to make it work.
For example, to active the frame object - use:

objFR.Activate

You can also call the InitializeEvents procedure from the
ThisWorkbook.Workbook_Activate event and the
ThisWorkbook.Workbook_SheetActivate event when you display the first
worksheet or change worksheets that will use the same code.

2. When adding/deleting objects, public variables that will be used from the
ThisWorkbook, Worksheet, Module, and Class Module get blown away for no
reason. For example, I was setting variables to handle highlighting of a
textbox within a specific frame whenever the worksheet was initially
displayed, redisplayed, or the a new frame/textbox/spinbutton object was
added. But the object that I was highlighting would lose cursor control and
highlighting when the object was added or another object was deleted. You
can get around this by using namedfields. For example:

objFR.Object.Controls(0).SelStart = 3
objFR.Object.Controls(0).SelLength = 2
objFR.Object.Controls(0).HideSelection = False
ThisWorkbook.Names.Add Name:="strtimechange", RefersTo:="MN",
Visible:=False
ThisWorkbook.Names.Add Name:="iCur", RefersTo:=3, Visible:=False

This will ensure the values of the variables are always retained. Just make
sure you recall the values when you need them using code like:

Dim strtimechange As string
Dim icur As Integer
strtimechange = Mid(ThisWorkbook.Names("strtimechange").RefersTo, 3,
Len(ThisWorkbook.Names("strtimechange").RefersTo) - 3)
iCur = Mid(ThisWorkbook.Names("icur").RefersTo, 2)

3. Use Application.EnableEvents, Application.ScreenUpdating, and
Application.Cursor around the code in your procedures to control events from
being repeated, screen from being repainted, and the cursor to show a
temporary "busy" cursor instead of the default.

Anyway - hope this helps - Alot of people helped me to get to this point and
I wanted to thank them by sharing what I learned to others in return.

Steve

P.S. Sorry if I left anything out when I editing the code to include in this
post. I can provide additional info to anyone that needs it.

 

[David Wilkinson]

Did you get this to work?

When I try it, everything seems to works. But, the events don't get fired..