Exclude TExt Boxes from Emplty control code

[chipsworld]

Found this little tid bit somewhere on the web and it works great, but was wondering if someone could tell me how to modify it so that I could not include certain textboxes (controls)

Dim t As Control

 For Each t In Me.Controls
If TypeName(t) = "TextBox" Then ' Make sure we're only looking at textboxes.
If t.Text = vbNullString And t.Tag <> vbNullString Then
MsgBox "All fields must be completed before proceeding!" & vbCrLf & vbCrLf & _
t.Tag, vbinfirmation, "Information: Incomplete Entry!"
t.SetFocus
End If
End If
    Next

    Exit Sub

    End If

 

[Norie]

How would you determine which textboxes to exclude?

By name?

 

[chipsworld]

I can use name, yes...

There are three text boxes that are very rarely used and have a null value unless used, so would want to skip them.

Weird thing is...these three are only caught when one of the others is caught. If the end user fills out everything else correctly, it will not catch that these are empty, but if they skip one, it will show the one they skipped and then these three ...

If it helps...the three are located in a frame that is not part of the standard tab order...

Not sure about that part either.

Here is the userform...the textboxes circled are the ones to skip over... again, they are in a frame...

 

[Norie]

I think part of the problem could be the frame.

If you want to loop through all the textboxes on the userform, including those in the frame, then you will need another loop to go through the controls collection of the frame.

Is there a reason those three controls are in the frame?

 

[chipsworld]

Yes...the three controls are very rarely used, so no need to include them in the normal tab order. I put them in the frame, and made the frame a non-tab stop so that the normal flow would go around it. If the three are used, the user manually selects the first one and enters the date, then the tab order inside the frame takes them to the next one for the number of days. The final one is not a tab stop either, but is calculated on the afterupdate from the second (1+2=3) and the focus is then moved to the CMD button "Click to Continue" so that the end user can just hit enter or click the button to continue on to the remaining portion of the process.
The weird part is that the code looking for null controls does not see them as null unless the user leaves one of the others null. Then it will see them...not sure why.

The real issue is that the msgboxes only show one null control at a time, and wont let you fix them until you have gone through all of the msgboxes. I would like to be able to exclude those three if possible. Another issue is that after the last msgbox is closed, the code sets the focus on the "last" control the msgbox alerts to which in this case is the locked control where the calculated value goes...

 

[Norie]

Are you looking for something that goes through the textboxes and highlights the ones that haven't been filled?

 

[chipsworld]

No...the code I use now finds the empty one, displays the field name based on the label text, and then sets the focus in that textbox. What I have works, as the end user is not going to forget more than one field (I hope) Your idea does sound intriguing however... Instead of a msgbox for each null control, just highlight the empty ones and display a single msgbox saying that there are fields that must be filled in...

I just need it to skip those three in its search for textboxes with a null value.

Is there a way to use a variable or ?? to "set" the control names for the code to use? (Just trying to think outside the box)

Maybe something like the bold below... This doesn't work, but is there a way to identify these to skip this way?

 For Each t In Me.Controls
If TypeName(t) = "TextBox" Then ' Make sure we're only looking at textboxes.
If t.Text = vbNullString And t.Tag <> vbNullString [B]And Not Me.txtpatstart And Not Me.txtpatend[/B] Then
MsgBox "All fields must be completed before proceeding!" & vbCrLf & vbCrLf & _
t.Tag, vbinfirmation, "Information: Incomplete Entry!"
t.SetFocus
End If
End If
    Next

    Exit Sub

    End If

My skill set is not high enough to reason this one through... Hence the reason I am asking you guys! LOL

 

[Norie]

You could set the Tag property of the controls you want to ignore/skip to something like 'Ignore' and then check for that in the first If.

Something like this.

If TypeName(t) = TextBox And t.Tag <> "Ignore" Then
   ' other code
End If

 

[chipsworld]

Norie...that is Brilliant! Works perfectly...

Learn something new every day!

I thank you very much... Have a safe and glorious day!

 

[Norie]

Eh, it's actually kind of wrong - TextBox should be in quotes.