Userform Data Validation

[vba_monkey]

Hello,

I have a userfom with a large number of fields in it. Most but not all have some form of validation on the fields which turns the background colour red if an illegal value is entered e.g. invalid dates etc.

I need to find a way that to throw up an error message if the user clicks submit when any of the fields are coloured red.

Does anyone know how to do this please?

 

[KKaren]

I would write something like this, but you will need to adjust it a little bit to your code...

Private Sub cmdSubmit_Click()
    
    Dim cCont As Control
    
    For Each cCont In Me.Controls
    
        If cCont.BackColor = rgbRed Then
            MsgBox "There are empty fields, pls fill in and try again"
            Exit Sub
        Else
            '.....your other code here

        End If
    Next cCont
    
End Sub

 

[vba_monkey]

Thank you, that works perfectly.

 

[KKaren]

Thank you, that works perfectly.

Well, actually there is a mistake in the above code, so you better use the one below:

Private Sub cmdSubmit_Click()
    
    Dim cCont As Control
    
    Dim i As Integer 'variable to count the fields in red as a number
    
    For Each cCont In Me.Controls
        If cCont.BackColor = rgbRed Then
            i = i + 1
        End If
    Next cCont
    
    
    If i > 0 Then
        MsgBox "Please fill in the fields in red and try again"
        Exit Sub
    Else
        'your other code here
    End If
    
End Sub

 

[vba_monkey]

What was wrong with your original code, it seems to produce the results I need?

 

[KKaren]

Hi,

If you use the first code which I pasted, if the very first control (or field) for example doesn't have a red background color, the visual basic editor will step into the else block and execute whatever commands you have there...And I believe this is not what you want...You can debug this and you should see that it's not working as anticipated.

Whereaz in the second code, first, the loop will be performed and Excel will count all fields with red color. And only then, it will check if i>0 (so there is at least one empty / invalid field). In such case a message will be shown to the user and the sub will be completed.
If i=0, i.e. no empty/invalid fields, then the visual basic editor will complete whatever commands you have for your submit button.

I had such issue with one of my tools at work...

I hope it's understandable

 

[vba_monkey]

OK that makes sense, thank you.

 

[gallen]

Can be simplified even further:

Private Sub cmdSubmit_Click()


Dim cCont As Control


    For Each cCont In Me.Controls
        If cCont.BackColor = rgbRed Then
           MsgBox "Please fill in the fields in red and try again"
           Exit Sub 'we have red so we failed
        End If
        
'your other code here


End Sub

 

[vba_monkey]

If I had three Userforms that I wanted it to check for red fields, would I need to repeat the above code each time for each form or can it be made to check all three at once?