At the end of my last post I said I’d be back with an actual use for a multiple-control event class. And here I am! It seems to me that validating controls in a userform is a very good use for this type of class. Not having the validation mixed in could really unclutter the main form and clarify things. So let’s look at a real-life application that I’ve just updated to this kind of control validation. It’s probably full of bugs, but what the heck.
The application is an addin for splitting worksheets into separate workbooks based on one or two columns. The process starts with a form where users choose the row with the headers and the column(s) to split on. The userform opens with the active row selected and the OK button disabled. When users select a primary column the OK button is activated. If they clear the row textbox, pick a secondary column without a primary column, or pick the same column for both, the OK button is disabled and an error message shows. This functionality is all handled in a multiple-control WithEvents class:
As you can see, there’s a textbox at the bottom to display the validation error message. In all three cases, in addition to showing the message, the OK button is disabled. My hope is that this provides a user-friendly experience: guiding them and letting them try different things without wasting their time pushing “OK” when it’s really not.
The class with the validation looks a lot like the multiple-control class from the last post. Just like there, every control’s click or change event calls the same procedure. In this case it’s a validation procedure, called “CheckReadyState.” It runs a few tests to make sure the various controls are in harmony. If so, the form’s “OK” button is enabled. If not, it’s disabled and a validation error message is shown. Here’s the whole class:
Private WithEvents lst As MSForms.ListBox
Private WithEvents chk As MSForms.CheckBox
Private WithEvents spn As MSForms.SpinButton
Private m_PassedControl As MSForms.Control
Private m_ParentForm As MSForms.UserForm
Property Set ctl(PassedControl As MSForms.Control)
Set m_PassedControl = PassedControl
Select Case True
Case TypeOf PassedControl Is MSForms.TextBox
Set txt = PassedControl
Case TypeOf PassedControl Is MSForms.ListBox
Set lst = PassedControl
Case TypeOf PassedControl Is MSForms.CheckBox
Set chk = PassedControl
Case TypeOf PassedControl Is MSForms.SpinButton
Set spn = PassedControl
End Select
Set m_ParentForm = GetParentForm(PassedControl)
End Property
Private Sub chk_Click()
CheckReadyState
End Sub
Private Sub lst_Change()
CheckReadyState
End Sub
Private Sub spn_Change()
CheckReadyState
End Sub
Private Sub txt_Change()
CheckReadyState
End Sub
Private Sub CheckReadyState()
Dim HelpMessage As String
Dim ReadyState As Boolean
ReadyState = True
With m_ParentForm
If .txtHeaderRowNum = "" Then
HelpMessage = "Pick a Header Row"
ReadyState = False
ElseIf .lstChooser2.ListIndex <> -1 And .lstChooser1.ListIndex = -1 Then
HelpMessage = "No Primary Column Picked"
ReadyState = False
ElseIf (.lstChooser1.ListIndex = .lstChooser2.ListIndex) And .lstChooser1.ListIndex >= 0 Then
HelpMessage = "Duplicate Columns"
ReadyState = False
Else
'If we got this far then there's no validation errors,
'so if they've selected at least a primary column we're ready for the OK button
ReadyState = .lstChooser1.ListIndex <> -1
End If
.cmdOk.Enabled = ReadyState
SetHelpMessage HelpMessage
End With
End Sub
Function GetParentForm(ctl As MSForms.Control) As MSForms.UserForm
Dim ParentForm As Object
Set ParentForm = ctl.Parent
Do Until TypeOf ParentForm Is MSForms.UserForm And Not TypeOf ParentForm Is MSForms.Frame
Set ParentForm = ParentForm.Parent
Loop
Set GetParentForm = ParentForm
End Function
Sub SetHelpMessage(HelpMessage As String)
m_ParentForm.lblError.Caption = HelpMessage
End Sub
The basic philosophy here is that a change to any control on the form, or, more accurately, any control having a WithEvents type in the public declarations of this class, triggers the validation routine. It doesn’t matter which control triggers it, the same things are validated. That distinguishes this class’s function, I hope, from the type of checking that syncs the spinbutton/textbox combo, for example what to do if the user tries to spin below zero. It also distinguishes the class functions from what happens when the OK button is clicked. Both those are handled within the userform itself.
Of course, the class does refer to individual controls on the userform as part of the validation, such as checking whether the same column was selected for both listboxes. Note that you can refer to the controls with something like “ParentForm.lstChooser1” although there’s no IntelliSense for them.
I also messed around with a different way to refer to the parent form within the class. As Ross commented on an older post, passing the form to the class seems kind of clunky. I came up with a new clunky way in the “GetParentForm” function. It starts with the calling control and recursively checks parent controls until it climbs to the userform level. As mentioned in my last post, frames are seen as both frame and userform controls when using TypeOf, hence the double check in the “Do Until” line. I could have just checked the control’s TypeName, as mentioned in the last post, but with a form TypeName doesn’t return “UserForm.” It returns “frmWorksheetSplitter” or whatever you called it.
This is all kind of experimental on my part, and I’m sure parts of it could be made more object-oriented or otherwise improved, so let me know. I do like having the validation off in it’s own world, instead of mixing it in with the rest of the form code.
The userform is similar to the one in A Flexible VBA Chooser Form. To see how it works, and how I populate the listboxes, and any number of wondrous things, download the workbook.
Doug – greetings from Mumbai India. Thanks again for a brilliant insight into how to economize and optimize code! Good on ya mate! Help me out please with something here – the cmdOk and cmdCancel are wrapped in a frame. Does that benefit the functionality in any way at all? What I saw is something to the contrary – where the validation procedure now needs to strip away the parent from the frame and ultimately reach the userform. And it appears that the frame has no useful purpose as far as the code goes. I am aware that wrapping option buttons in a frame introduces an automated alternating mechanism by unchecking all other option buttons within the same frame when a particular option button is clicked. Please help me out with the utility of doing this to command buttons
Additionally, what is the merit of Getparent? There is only one instance of a userform being initialized everytime the code is run. So pretty much it is clear even at the design time what the parent (or ultimate parent) of every control is. What would be the result if this part of the functionality is ignored altogether while coding the utility? Thanks a lot again – there is heaps I am learning from the examples you have put up!!
Maybe the second part of my question could have been more explicit. The UF code already declares an instance of the userform (ufWorksheetSplitter). Could this variable be made visible across modules and used for the formvalidation component? Ok, so maybe variables declared at the module level in normal code modules cannot be accessed from class modules? That could be one explanation ……….. will have to try this for myself!
Which I just did – I moved the Dim ufworksheetsplitter to the top of the module and declared it as public. Then in the form validation module I hooked the m_parentform to ufworksheetsplitter with a Set m_parentform = ufworksheetsplitter. The code continued to run as it is meant to…..What am I missing here Doug?
No good reason for the frame.
For your second question, it’s generally considered best practice to avoid global variables, and to pass them instead. This is my workaround to not being able to pass the form to the class.
Just to be clear, when I hooked up the m_parentname with ufworksheetsplitter, I also commented out your line (‘m_parentname = getparentform (passedcontrol) as well as the entire GetParentForm function.
So I stumbled upon this post while researching MVP models in VBA for Forms. I like being able to remove the heavy work off the form and put into a class like this. I however changed the CheckReadyState procedure to use error levels rather than a long list of If..ElseIf. This made it easier to do the logic on some my code and also easier to add new validations.
Private Sub CheckReadyState()
Dim HelpMessage As String
Dim ReadyState As Boolean
On Error GoTo ValidationError
With ParentForm
If .txtHeaderRowNum = “” Then
Err.Raise Number:=vbObjectError + 42, Description:=”Pick a Header Row”
End If
If .lstChooser2.ListIndex -1 And .lstChooser1.ListIndex = -1 Then
Err.Raise Number:=vbObjectError + 42, Description:=”No Primary Column Picked”
End If
If (.lstChooser1.ListIndex = .lstChooser2.ListIndex) And .lstChooser1.ListIndex >= 0 Then
Err.Raise Number:=vbObjectError + 42, Description:=”Duplicate Columns”
End If
End With
ReadyState = True
ValidationExit:
With ParentForm
.cmdOk.Enabled = ReadyState
.lblError.Caption = HelpMessage
End With
Exit Sub
ValidationError:
ReadyState = False
HelpMessage = Err.Description
Resume ValidationExit
End Sub
Hi Nick, Thanks for sharing your code. Those look like excellent tweaks!