TextBox SetFocus in Controls Loop

[ldoodle]

Hello,

I have a userform for text input - 3 text boxes. Each must meet a certain criteria to be passed as ok. All this is done and working.

What I am struggling with though is set focus. What I want, is if the first text box is passed OK but the other 2 aren't, the first text box with the error should be given focus. If text box 2 is ok but 1 and 3 are wrong, 1 should be given focus. If 1 and 2 are ok and 3 is wrong, 3 should have focus. And so on.

I am doing this in a controls loop, then passing the text box object to a procedure which does the error checking and set the captions of appropriate labels and changes the border colour of the text box.

As the SetFocus is currently part of the loop, it always sets focus to the last box with the error, because it just carries on the loop.

I can't quite work out how to stop it continuing to set focus once focus has already been set. I don't want to exit the loop once an error is encountered because I want all error fields to show on in one go when pressing OK.

Any ideas? In VBA can you find the first control that matches type and has certain criteria (i.e. specific border colour)? Without another loop?

If you can, I could do all the error checking and flagging first, then something like this:

Control.Parent.Controls(first textbox with red border).SetFocus

Thanks

[ldoodle]

    With Me
    
        For Each Control In .Controls
        
            If TypeName(Control) = "TextBox" And Control.Enabled = True Then Call ValidateInput(Control)
            
        Next Control
        
    End With

I've simplified this bit:

Public Sub ValidateInput(ByRef Control As MSForms.Control)

    If Control.Value = "" then Control.BorderColour = vbRed
    
End Sub

[BadlySpelledBuoy]

Perhaps loop through them in reverse? That way the last one found with an error would be the earliest on the form from the user's point of view.

BSB

[Andy Pope]

Turn your routine into a function and store reference to first occurance.

    Dim firstCtl as msforms.control

    set firstCtl = nothing
    With Me
    
        For Each Control In .Controls
        
            If TypeName(Control) = "TextBox" And Control.Enabled = True Then 
                If ValidateInput(Control) then
                    if firstCtl is nothing then
                      Set firstCtl = control
                   end if
                End if            
        Next Control
        if not firstCtl is nothing then
            firstCtl.SetFocus
        end if
    End With

Public Function ValidateInput(ByRef Control As MSForms.Control) as Boolean

   Dim returnValue as boolean
   
returnvalue=false
    If Control.Value = "" then 
        Control.BorderColour = vbRed
        returnValue = true
    endif

    ValidateInput = returnValue
End Function

[ldoodle]

Thanks both