If Then's for 12 textboxes to check if they are empty.

I have 12 textboxes for data on a userform. Before writing the data to the
worksheet I need to check whether the text boxes are empty or not. If empty
then a message pops up to enter data into the textbox with focus.
Do I need to write 12 separate if then’s? Or can I apply an easier quicker
method?

If you have 14 textboxes and you only have to validate 12 of them, then I'd
either use nice names and loop through the nice names (tb01 through tb12).

But if you want to check all the textboxes (no matter how many), you could put
your validation into the "ok" button. (I added a label to show any error.)

Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim ErrorFound As Boolean

ErrorFound = False
Me.Label1.Caption = ""
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.TextBox Then
If ctrl.Object.Value = "" Then
ErrorFound = True
ctrl.SetFocus
Me.Label1.Caption = "Please fix textboxes!"
Exit For
End If
End If
Next ctrl

If ErrorFound = True Then
Exit Sub
End If

'rest of code

End Sub



Beertje wrote:
>
> I have 12 textboxes for data on a userform. Before writing the data to the
> worksheet I need to check whether the text boxes are empty or not. If empty
> then a message pops up to enter data into the textbox with focus.
> Do I need to write 12 separate if then’s? Or can I apply an easier quicker
> method?

--

Dave Peterson

Dave,
thx.
What doe the statement: Me.Label1.Caption = "" do?
Is there a way to highlight the Textbox which is not filled? i.e. different
color?
rgds,

Alex.


"Dave Peterson" wrote:

> If you have 14 textboxes and you only have to validate 12 of them, then I'd
> either use nice names and loop through the nice names (tb01 through tb12).
>
> But if you want to check all the textboxes (no matter how many), you could put
> your validation into the "ok" button. (I added a label to show any error.)
>
> Option Explicit
> Private Sub CommandButton1_Click()
> Dim ctrl As Control
> Dim ErrorFound As Boolean
>
> ErrorFound = False
> Me.Label1.Caption = ""
> For Each ctrl In Me.Controls
> If TypeOf ctrl Is MSForms.TextBox Then
> If ctrl.Object.Value = "" Then
> ErrorFound = True
> ctrl.SetFocus
> Me.Label1.Caption = "Please fix textboxes!"
> Exit For
> End If
> End If
> Next ctrl
>
> If ErrorFound = True Then
> Exit Sub
> End If
>
> 'rest of code
>
> End Sub
>
>
>
> Beertje wrote:
> >
> > I have 12 textboxes for data on a userform. Before writing the data to the
> > worksheet I need to check whether the text boxes are empty or not. If empty
> > then a message pops up to enter data into the textbox with focus.
> > Do I need to write 12 separate if then’s? Or can I apply an easier quicker
> > method?
>
> --
>
> Dave Peterson
>

I like to show warning messages in labels.

This line:
Me.Label1.Caption = ""
just clears out the text in that label--so if there is no error, you don't see
an old warning.

I would have guessed that selecting that textbox would be sufficient(??), but
next time you're in design mode, click on one of the textboxes.

Hit F4 to see its properties.
Look for backcolor and change it to the color you like.

Note what that number is and then change it back to its original color.

I used dark blue (hard to see the text).

Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim ErrorFound As Boolean

ErrorFound = False
Me.Label1.Caption = ""
For Each ctrl In Me.Controls
ctrl.Object.BackColor = &H80000005
If TypeOf ctrl Is MSForms.TextBox Then
If ctrl.Object.Value = "" Then
ErrorFound = True
ctrl.SetFocus
ctrl.Object.BackColor = &H80000002
Me.Label1.Caption = "Please fix textboxes!"
Exit For
End If
End If
Next ctrl

If ErrorFound = True Then
Exit Sub
End If

'rest of code

End Sub

Beertje wrote:
>
> Dave,
> thx.
> What doe the statement: Me.Label1.Caption = "" do?
> Is there a way to highlight the Textbox which is not filled? i.e. different
> color?
> rgds,
>
> Alex.
>
> "Dave Peterson" wrote:
>
> > If you have 14 textboxes and you only have to validate 12 of them, then I'd
> > either use nice names and loop through the nice names (tb01 through tb12).
> >
> > But if you want to check all the textboxes (no matter how many), you could put
> > your validation into the "ok" button. (I added a label to show any error.)
> >
> > Option Explicit
> > Private Sub CommandButton1_Click()
> > Dim ctrl As Control
> > Dim ErrorFound As Boolean
> >
> > ErrorFound = False
> > Me.Label1.Caption = ""
> > For Each ctrl In Me.Controls
> > If TypeOf ctrl Is MSForms.TextBox Then
> > If ctrl.Object.Value = "" Then
> > ErrorFound = True
> > ctrl.SetFocus
> > Me.Label1.Caption = "Please fix textboxes!"
> > Exit For
> > End If
> > End If
> > Next ctrl
> >
> > If ErrorFound = True Then
> > Exit Sub
> > End If
> >
> > 'rest of code
> >
> > End Sub
> >
> >
> >
> > Beertje wrote:
> > >
> > > I have 12 textboxes for data on a userform. Before writing the data to the
> > > worksheet I need to check whether the text boxes are empty or not. If empty
> > > then a message pops up to enter data into the textbox with focus.
> > > Do I need to write 12 separate if then’s? Or can I apply an easier quicker
> > > method?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson