no empty values in inputbox

[bramnizzle]

Hey everyone...
I'm working on a macro with an inputbox and I don't want there to be
any empty boxes. I want the user to be forced to put *something* in
the boxes before the code will continue.

I'm imagining it has to go somewhere in my FOR NEXT loop...

For Each cItem In Me.Controls
If TypeName(cItem) = "TextBox" Then
'another if statement here? PLEASE HELP!!
sSPN = sSPN + cItem.Value + "-"
End If
Next cItem

 

[paul.robinson]

Hey everyone...
I'm working on a macro with an inputbox and I don't want there to be
any empty boxes.  I want the user to be forced to put *something* in
the boxes before the code will continue.

I'm imagining it has to go somewhere in my FOR NEXT loop...

For Each cItem In Me.Controls
    If TypeName(cItem) = "TextBox" Then
        'another if statement here?  PLEASE HELP!!
        sSPN = sSPN + cItem.Value + "-"
    End If
Next cItem

Hi

For Each cItem In Me.Controls
If TypeName(cItem) = "TextBox" Then
If Trim(cItem.Value) = "" then
MsgBox "No Text Box may be left empty"
Exit Sub
end if
sSPN = sSPN + cItem.Value + "-"
End If
Next cItem

regards
Paul

 

[JLGWhiz]

It is difficult to tell how you have the input box set up, but I will try to
give you a general solution that you can apply. Normally, you would set up
your validation loop to fire immediately after the user makes or does not
make the input. Here is one example.
RETRY:
myResult = InputBox("Enter something", "Title")
If myResult = "" Or myResult = False Then
GoTo RETRY:
Else
'You regular code
End If

This will force them to put something in the input box. The problem is,
that they could put anything in there and that might cause an error in the
regular processing.
If you have specific data that needs to be entered that the user could
choose from, it might be better to build a UserForm that provides the user
with a list to select from and control their selection that way.