Loop

[daren white]

I have a userform with comboboxes set as dropdown list. I want to check when
the command button is pushed that the boxes are not blank.

my code is

Dim boolExit As Boolean
Do
If ComboBox1.Value = "" Then
MsgBox "field is blank", vbCritical
boolExit = True
ElseIf TextBox3.Value = "" Then
MsgBox "field is blank", vbCritical
boolExit = True
ElseIf ComboBox2.Value = "" Then
MsgBox "field is blank", vbCritical
boolExit = True
ElseIf ComboBox4.Value = "" Then
MsgBox "field is blank", vbCritical
boolExit = True
ElseIf ComboBox5.Value = "" Then
MsgBox "field is blank", vbCritical
boolExit = True
ElseIf ComboBox6.Value = "" Then
MsgBox "field is blank", vbCritical
boolExit = True
ElseIf ComboBox3.Value = "" Then
MsgBox "field is blank", vbCritical
boolExit = True
End If
Loop Until boolExit

which comes up with the error when the box is blank and then hangs, I cannot
access the userform to change the field

 

[Greg Maxey]

Try something like this:

Private Sub CommandButton1_Click()
Dim oCtr As Control
For Each oCtr In Me.Controls
If TypeName(oCtr) = "ComboBox" Then
If oCtr.ListIndex = -1 And oCtr.Text = "" Then
MsgBox "Please fill in all comboboxes", vbCritical
Exit For
End If
End If
Next
'Do something
End Sub

 

[daren white]

the only problem i have is that some comboboxes do not need to be checked

 

[Greg Maxey]

Sorry. I tried. You are welcome for the effort. I guess you will have to
try something else.

 

[Greg Maxey]

Sorry. I tried. You are welcome for the effort. I guess you will have to
try something else.

 

[daren white]

thanks for your help. Can you tell me how the do and loop until commands
work. When it finds a boolexit = true does it start from the beginning or
from the line where the loop ended?

 

[Doug Robbins - Word MVP]

You do not need a Loop construction. It's the If ElseIf End If construction
that is doing the work.

You should give meaningful names to all of the controls. Your MsgBox "field
is blank", vbCritical is not particularly helpful to the user. Which field
is blank?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[daren white]

the problem I have is I need to check the comboboxes are not blank first and
then depending on the answers in the comboboxes I need to open different
userfoms which is why I wanted to loop the if statements to see if the boxes
were empty

 

[David Horowitz]

I think for that, we'd need to know what function this code is in - is it a
button click handler, like cmdOK_Click? How do you hide your form, if you
do? Just don't open up those other forms if these fields fail.
How about something more like a separate validation function you can call
which returns a Boolean, like:

Function ValidateFields() As Boolean
' We'll exit if we fail validation with a default return value of False.
' If we get through all the tests, we'll return True at the end.
If ComboBox1.Value = "" Then
MsgBox "combo1 is blank", vbCritical
exit function
ElseIf TextBox3.Value = "" Then
MsgBox "text3 is blank", vbCritical
exit function
ElseIf ComboBox2.Value = "" Then
....
End If
ValidateFields = True ' we passed all tests!
End Function

Then, at the appropriate time, you'd call it:
Private Sub cmdOK_Click()
if ValidateFields() then
' OK to move on here, so hide this form, pop up the other forms, etc.
end if
Exit Sub
HTH.

 

[Doug Robbins - Word MVP]

With the code that you have shown us, you do NOT need the Loop. If one of
the conditions for BoolExit to be set to True is encountered by the
IF....ELSEIF...END IF construction, then that is what it will be set to.

The following two constructions produce exactly the same result

Dim boolExit As Boolean
Do While boolExit = False
If 1 = 2 Then
boolExit = True
MsgBox "One"
ElseIf 1 = 2 Then
boolExit = True
MsgBox "Two"
ElseIf 1 = 1 Then
boolExit = True
MsgBox "Three"
ElseIf 1 = 2 Then
boolExit = True
MsgBox "Four"
End If
Loop

Dim boolExit As Boolean
If 1 = 2 Then
boolExit = True
MsgBox "One"
ElseIf 1 = 2 Then
boolExit = True
MsgBox "Two"
ElseIf 1 = 1 Then
boolExit = True
MsgBox "Three"
ElseIf 1 = 2 Then
boolExit = True
MsgBox "Four"
End If


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[David Horowitz]

Yes, you could in fact just remove the Do and the Loop and use your boolExit
variable as your indicator that a field was empty...

 

[Gordon Bentley-Mix]

Daren,

I'd probably use a Boolean function instead of a variable. It's easier to
get your head around, and evaluating the value of the will call it
automatically.

Assuming a minimum of seven ComboBoxes with selection required for all
except the third and a minimum of five TextBoxes with a value required only
for the fifth, something like the following should work:

Private Sub btnOK_Click()
If fcnValidateInput = True Then
'Do the stuff that should be done if the values are all OK
'like hide the current UserForm, show the other UserForms, etc.
End If
End Sub

Private Function fcnValidateComboBoxes() As Boolean
fcnValidateComboBoxes = True
If Len(Trim(ComboBox1.Value)) = 0 Then
MsgBox "ComboBox1 cannot be blank.", vbCritical, "Input Error"
fcnValidateComboBoxes = False
Exit Function
End If
If Len(Trim(ComboBox2.Value) )= 0 Then
MsgBox "ComboBox2 cannot be blank.", vbCritical, "Input Error"
fcnValidateComboBoxes = False
Exit Function
End If
If Len(Trim(TextBox5.Value)) = 0 Then
MsgBox "TextBox5 cannot be blank.", vbCritical, "Input Error"
fcnValidateComboBoxes = False
Exit Function
End If
If Len(Trim(ComboBox4.Value)) = 0 Then
MsgBox "ComboBox4 cannot be blank.", vbCritical, "Input Error"
fcnValidateComboBoxes = False
Exit Function
End If
If Len(Trim(ComboBox6.Value)) = 0 Then
MsgBox "ComboBox6 cannot be blank.", vbCritical, "Input Error"
fcnValidateComboBoxes = False
Exit Function
End If
If Len(Trim(ComboBox7.Value)) = 0 Then
MsgBox "ComboBox7 cannot be blank.", vbCritical, "Input Error"
fcnValidateComboBoxes = False
Exit Function
End If
End Function

Note the use of the Len function to see if the specified controls are blank.
This is more efficient than using a .Value = "" construction. In addition,
the use of the Trim function ensures that value isn't just one or more empty
spaces.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Gordon Bentley-Mix]

I really hate that I can't edit my posts after I post them...

Let's try that code again. It should be like this:

Private Sub btnOK_Click()
If fcnValidateInput = True Then
'Do the stuff that should be done if the values are all OK
'like hide the current UserForm, show the other UserForms, etc.
End If
End Sub

Private Function fcnValidateInput() As Boolean
fcnValidateInput = True
If Len(Trim(ComboBox1.Value)) = 0 Then
MsgBox "ComboBox1 cannot be blank.", vbCritical, "Input Error"
fcnValidateInput = False
Exit Function
End If
If Len(Trim(ComboBox2.Value) )= 0 Then
MsgBox "ComboBox2 cannot be blank.", vbCritical, "Input Error"
fcnValidateInput = False
Exit Function
End If
If Len(Trim(TextBox5.Value)) = 0 Then
MsgBox "TextBox5 cannot be blank.", vbCritical, "Input Error"
fcnValidateInput = False
Exit Function
End If
If Len(Trim(ComboBox4.Value)) = 0 Then
MsgBox "ComboBox4 cannot be blank.", vbCritical, "Input Error"
fcnValidateInput = False
Exit Function
End If
If Len(Trim(ComboBox6.Value)) = 0 Then
MsgBox "ComboBox6 cannot be blank.", vbCritical, "Input Error"
fcnValidateInput = False
Exit Function
End If
If Len(Trim(ComboBox7.Value)) = 0 Then
MsgBox "ComboBox7 cannot be blank.", vbCritical, "Input Error"
fcnValidateInput = False
Exit Function
End If
End Function

Also
"...and evaluating the value of the will call it..."
should read
"...and evaluating the value of the function will call it..."

Sorry. Just washed my hands and I can't do a thing with 'em. ;-P
--
Cheers!

Gordon Bentley-Mix
Word MVP

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[daren white]

Thanks for that - it works a treat. These forums are so helpful

 

[Doug Robbins - Word MVP on news.microsoft.com]

Glad that you got there in the end.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com