Linking Userforms and Worksheets: Part IV

Part I: Setting Up the Form
Part II: Helper Procedures
Part III: Determining If the Form Needs to be Saved

In this part, I will discuss the form’s Initialize event, i.e. what happens when it’s started up.

First, we’ll need another module level variable:

Private mcControls As Collection

The Initialize Event

Private Sub UserForm_Initialize()
Dim ctlInfo As Control
Dim clsEvents As CControlEvents

'A module level collection so the classes don’t
'lose scope
Set mcControls = New Collection

'Fill the combobox with a list of state abbreviations
'on a hidden worksheet
Me.cbxState.List = wksData.Range("States").Value

'Loop through the controls on the form
For Each ctlInfo In Me.Controls
'Controls with a numeric tag are data entry controls
'and that’s what we want
If IsNumeric(ctlInfo.Tag) Then
'Create a new class
Set clsEvents = New CControlEvents
'Determine the type of control, set the public
'withevents class variable to the control, and
'add the class to the collection so it won’t go
'out of scope while the form is shown.
Select Case TypeName(ctlInfo)
Case "TextBox"
Set clsEvents.gTextBox = ctlInfo
mcControls.Add clsEvents, CStr(ctlInfo.Tag)
Case "ComboBox"
Set clsEvents.gCombo = ctlInfo
mcControls.Add clsEvents, CStr(ctlInfo.Tag)
End Select
End If
Next ctlInfo

'Self-documenting helper sub names - you gotta love that
DefineScroll

'Start at the first record
Me.scbContact.Value = Me.scbContact.Min

End Sub

The comments should explain what’s going on here. Generally, it loops through the data entry controls on the form and creates an instance of the class so the events will fire and change the IsDirty property. It also does some general start up stuff like setting the initial value of the scrollbar.

Note that the PopulateRecord helper sub is executed twice, once from the DefineScroll helper sub and once from setting the value of the scrollbar. Both of these trigger the change event of the scrollbar. You could include some code to stop that redundancy, but I didn’t want to muddy this up any more than it is.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.