(Solved) – How do I validate userform inputs where controls are dynamic using MVP approach?



I use one UserForm that has a dynamic assignment of its own caption and some control captions with three different variations. Specifically on this UserForm, four CheckBox are required on two variations and are not visible on one.

My data validation checks all mandatory fields have a value entered (including one of those four checkboxes are checked) so when using the form that does not require a checkbox be ticked (as the controls are not visible) I’m getting my "Please enter a value into each field." MessageBox.

How can I avoid this?

I’ve been having a read of UserForm1.Show over the past few months which for myself, along with many others, has helped me understand what a UserForm is and how it works.

I’m trying to implement the MVP pattern into an existing project I have which had more or less just been ‘completed’.

Naturally as I run into problems or confusions, I’ll jump to google and in most cases either find another article or a SO question with a more than adequate answer from the author. But. I can’t find one for validating an MSForms.Control that may or may not be there – i.e is sometimes used on a form, depending on what variation of the form it is.

Please note, I feel that I’m probably in the wrong with how I’ve designed my forms (well, singular form), so if that is the case, an answer that identifies and covers that topic would be most helpful also!

So here is my base form (test button is for…testing):
snip of design view of userform

And when any of these 3 buttons are clicked (worksheet ActiveX commandbuttons), it’s populated with one of the below UserForms (Captions correspond with buttons):
Worksheet ActiveX Commandbuttons

NEC userform LG userform Other userform

Now, my data validation works fine for the NEC and LG forms, but fails when it gets to the Other form. This is because one Product Type CheckBox is required for the NEC and LG products, but not for the Other products and the data validation fails if there is no Product Type.

Here I’ll include the CommandButton1_Click (test button) event and the class module code. My data validation is done in the UserForm module but I was recently reading i should put it in the Model so I think I need to move it to the Module doing all the other things.

UserForm Code Module – MCVE

Option Explicit
Public DataEntryForm As New TestForm

Private Sub CommandButton1_Click()

With Me
    If .CheckBox1.Value = True Then
        DataEntryForm.TestProduct = .CheckBox1.Caption
    ElseIf .CheckBox2.Value = True Then
        DataEntryForm.TestProduct = .CheckBox2.Caption
    ElseIf .CheckBox3.Value = True Then
        DataEntryForm.TestProduct = .CheckBox3.Caption
    ElseIf .CheckBox4.Value = True Then
        DataEntryForm.TestProduct = .CheckBox4.Caption
    End If
End With

If Not FormIsComplete Then
    MsgBox "Please enter a value into each field.", vbCritical, "Missing Values"
    Exit Sub
End If

End Sub

Private Function FormIsComplete() As Boolean
FormIsComplete = False

If DataEntryForm.TestProduct = "" Then Exit Function

FormIsComplete = True

End Function

Class Module – TestForm (MCVE)

Private pTestProduct As String

Public Property Get TestProduct() As String
    TestProduct = pTestProduct
End Property
Public Property Let TestProduct(NewValue As String)
    pTestProduct = NewValue
End Property

So, more specifically;

The problem lies with DataEntryForm.TestProduct. It is within the IsFormCompleted function as 2/3 forms require this property to have a value, but naturally isn’t required for the form without any Product Types.

My thoughts are the easy fix is to create another separate form for the Other Products version which can have a separate data validation function, but I want to try keep maintainability and avoid having more than 1 of this form.

How can I have this type of data validation adapt to recognise if the control should have a value or not?

Leave a Reply

Your email address will not be published. Required fields are marked *