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.
i have put a combobox in a form and i want to link this combobox to a range of cells in a worksheet. from the dropdown in the combobox a list should show the texts in that cells. pls help me with the code thanxs
It’s right up near the top:
‘Fill the combobox with a list of state abbreviations
‘on a hidden worksheet
Me.cbxState.List = wksData.Range(”States”).Value
cbxState is the name of the combobox, and wksData is the codename or variable name of the worksheet, which has a defined range name “States”.