Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 thens? Or can I apply an easier quicker method? |
#2
|
|||
|
|||
If Then's for 12 textboxes to check if they are empty.
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 thens? Or can I apply an easier quicker method? -- Dave Peterson |
#3
|
|||
|
|||
If Then's for 12 textboxes to check if they are empty.
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 |
#4
|
|||
|
|||
If Then's for 12 textboxes to check if they are empty.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
check boxes - copy | Excel Discussion (Misc queries) | |||
How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) | |||
How do I use a check box to accept a calculation | Excel Discussion (Misc queries) | |||
In Bar Chart, can we display both figures and their respective %a. | Excel Discussion (Misc queries) | |||
Creating a check box that does not require security clearance. | Excel Worksheet Functions |