I have a userform with several textboxes for data input. Before the form is
closed I would like to check that all textboxes contain data. If one or more
is blank, I would like to cancel the close and alert the user.

I suspect I need a statement in the QueryClose event and I was trying to use
something like this:

For Each Textbox in Userform
(Statements)
Next

This doesn't work "Object doesn't support this property or method". I
suppose I'm having difficulty understanding what a collection is. Whatever my
lack of understanding, any help to check a userform for blank textboxes
before closing the form?

Thanks
Paul

This might work.

For Each Textbox in Userform.Controls
(Statements)
Next


"Paul D." wrote:

I have a userform with several textboxes for data input. Before the form is
closed I would like to check that all textboxes contain data. If one or more
is blank, I would like to cancel the close and alert the user.

I suspect I need a statement in the QueryClose event and I was trying to use
something like this:

For Each Textbox in Userform
(Statements)
Next

This doesn't work "Object doesn't support this property or method". I
suppose I'm having difficulty understanding what a collection is. Whatever my
lack of understanding, any help to check a userform for blank textboxes
before closing the form?

Thanks
Paul

Textboxes are controls. You can use this statment.
For Each cntl In UserForm1.Controls


I reverse engineer these questions.

I put the following into my test code

sub test()
set a = Userform1
end sub

Then I step through the code and add a to the watch window until I find why
the text boxes are located. I created the userform1 with two text boxes.


"Paul D." wrote:

I have a userform with several textboxes for data input. Before the form is
closed I would like to check that all textboxes contain data. If one or more
is blank, I would like to cancel the close and alert the user.

I suspect I need a statement in the QueryClose event and I was trying to use
something like this:

For Each Textbox in Userform
(Statements)
Next

This doesn't work "Object doesn't support this property or method". I
suppose I'm having difficulty understanding what a collection is. Whatever my
lack of understanding, any help to check a userform for blank textboxes
before closing the form?

Thanks
Paul

That does work.
I thought that would be the hard part but I guessed wrong. That gets me into
the ForEach loop but now I need to check each Textbox value to see if it = "".
I was hoping that once in the loop:

if Textbox.Value = "" then

would find any blank textboxes, but instead I get the error "Object doesn't
support this property or method" for the above statement.

Any More help?
Last question ... promise!

"JLGWhiz" wrote:

This might work.

For Each Textbox in Userform.Controls
(Statements)
Next


"Paul D." wrote:

I have a userform with several textboxes for data input. Before the form is
closed I would like to check that all textboxes contain data. If one or more
is blank, I would like to cancel the close and alert the user.

I suspect I need a statement in the QueryClose event and I was trying to use
something like this:

For Each Textbox in Userform
(Statements)
Next

This doesn't work "Object doesn't support this property or method". I
suppose I'm having difficulty understanding what a collection is. Whatever my
lack of understanding, any help to check a userform for blank textboxes
before closing the form?

Thanks
Paul

Paul,

Try this:

Dim ctl As Control
For Each ctl In Me.Controls 'assumes code is in the Userform
If TypeOf ctl Is msforms.TextBox Then
If ctl.Value = "" Then
MsgBox "not ready"
End If
End If
Next

hth,

Doug

"Paul D." wrote in message
...
That does work.
I thought that would be the hard part but I guessed wrong. That gets me
into
the ForEach loop but now I need to check each Textbox value to see if it =
"".
I was hoping that once in the loop:

if Textbox.Value = "" then

would find any blank textboxes, but instead I get the error "Object
doesn't
support this property or method" for the above statement.

Any More help?
Last question ... promise!

"JLGWhiz" wrote:

This might work.

For Each Textbox in Userform.Controls
(Statements)
Next


"Paul D." wrote:

I have a userform with several textboxes for data input. Before the
form is
closed I would like to check that all textboxes contain data. If one or
more
is blank, I would like to cancel the close and alert the user.

I suspect I need a statement in the QueryClose event and I was trying
to use
something like this:

For Each Textbox in Userform
(Statements)
Next

This doesn't work "Object doesn't support this property or method". I
suppose I'm having difficulty understanding what a collection is.
Whatever my
lack of understanding, any help to check a userform for blank textboxes
before closing the form?

Thanks
Paul

Thank you, userform1.controls works.

I don't follow your reverse engineering. Can you share an example of how VB
can check for blank textboxes once into the For Each loop? Or are you
suggesting not using the For Each but some other way of programatically
checking for blank textboxes?

Paul
"Joel" wrote:

Textboxes are controls. You can use this statment.
For Each cntl In UserForm1.Controls


I reverse engineer these questions.

I put the following into my test code

sub test()
set a = Userform1
end sub

Then I step through the code and add a to the watch window until I find why
the text boxes are located. I created the userform1 with two text boxes.


"Paul D." wrote:

I have a userform with several textboxes for data input. Before the form is
closed I would like to check that all textboxes contain data. If one or more
is blank, I would like to cancel the close and alert the user.

I suspect I need a statement in the QueryClose event and I was trying to use
something like this:

For Each Textbox in Userform
(Statements)
Next

This doesn't work "Object doesn't support this property or method". I
suppose I'm having difficulty understanding what a collection is. Whatever my
lack of understanding, any help to check a userform for blank textboxes
before closing the form?

Thanks
Paul

On Oct 20, 8:37 pm, Paul D. wrote:
Thank you, userform1.controls works.

I don't follow your reverse engineering. Can you share an example of how VB
can check for blank textboxes once into the For Each loop? Or are you
suggesting not using the For Each but some other way of programatically
checking for blank textboxes?

Paul

"Joel" wrote:
Textboxes are controls. You can use this statment.
For Each cntl In UserForm1.Controls

I reverse engineer these questions.

Hello Paul,


Here is another method to check if the Form is filled in. Copy this
macro to the "General" Declarations section of the UserForm. Then
place a call in the UserForm_QueryClose event.

Function FormFilledIn() As Boolean

Dim Ctrl As Object

For Each Ctrl In Controls
If TypeName(Ctrl) = "TextBox" And Ctrl.Value = "" Then
MsgBox "You have not filled in the Form Completely",
vbExclamation
FormFilledIn = False
Exit Function
End If
Next Ctrl

FormFilledIn = True

End Function

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
If Not FormFilledIn Then Cancel = True
End Sub

Sincerely,
Leith Ross

Function FormFilledIn() As Boolean

Dim Ctrl As Object

For Each Ctrl In Controls
If TypeName(Ctrl) = "TextBox" And Ctrl.Value = "" Then
MsgBox "You have not filled in the Form Completely",
vbExclamation
FormFilledIn = False
Exit Function
End If
Next Ctrl

FormFilledIn = True

End Function

In the above function, you need to change the If-Then block from this
structure...

If TypeName(Ctrl) = "TextBox" And Ctrl.Value = "" Then

to this instead....

If TypeName(Ctrl) = "TextBox" Then
If Ctrl.Value = "" Then
<Code
End If
End If

otherwise it will "error out" when it comes across a control on the UserForm
that does not have a Value property, such as a CommandButton or Label. (VB
does not have short-circuit evaluation of logical statements, so both
conditions are tested even if the first condition is False.)

By the way, another way to test if the control is a TextBox is like this...

If TypeOf Ctrl Is TextBox Then

I like this way better myself because no part of it is case-sensitive (like
the TypeName property test is).

Rick

My comment about reverse engineering was to the fact that I didn't know that
Textboxes are controls. by looking at the watch window I found the textboxes
was a type of control.

The FOR statement basically transverse items. The text boxes in the wztch
window under control and each were assigned to an item. I also looked
(didn't find) if there was a type such as msiotextbox tgo distinquish the
text box from other types of controls.

When working with object on worksheets there are many type of shapes
(pictures, boxes, rectangles) and I use the type to make sure I'm looking at
the correct items.

"Paul D." wrote:

Thank you, userform1.controls works.

I don't follow your reverse engineering. Can you share an example of how VB
can check for blank textboxes once into the For Each loop? Or are you
suggesting not using the For Each but some other way of programatically
checking for blank textboxes?

Paul
"Joel" wrote:

Textboxes are controls. You can use this statment.
For Each cntl In UserForm1.Controls


I reverse engineer these questions.

I put the following into my test code

sub test()
set a = Userform1
end sub

Then I step through the code and add a to the watch window until I find why
the text boxes are located. I created the userform1 with two text boxes.


"Paul D." wrote:

I have a userform with several textboxes for data input. Before the form is
closed I would like to check that all textboxes contain data. If one or more
is blank, I would like to cancel the close and alert the user.

I suspect I need a statement in the QueryClose event and I was trying to use
something like this:

For Each Textbox in Userform
(Statements)
Next

This doesn't work "Object doesn't support this property or method". I
suppose I'm having difficulty understanding what a collection is. Whatever my
lack of understanding, any help to check a userform for blank textboxes
before closing the form?

Thanks
Paul

Just to add to an excellent answer -
Since Excel has a textbox object and the object on the userform is not that
object, it might be better to qualify

If TypeOf Ctrl Is Msforms.TextBox Then

--
Regards,
Tom Ogilvy



"Rick Rothstein (MVP - VB)" wrote:

Function FormFilledIn() As Boolean

Dim Ctrl As Object

For Each Ctrl In Controls
If TypeName(Ctrl) = "TextBox" And Ctrl.Value = "" Then
MsgBox "You have not filled in the Form Completely",
vbExclamation
FormFilledIn = False
Exit Function
End If
Next Ctrl

FormFilledIn = True

End Function

In the above function, you need to change the If-Then block from this
structure...

If TypeName(Ctrl) = "TextBox" And Ctrl.Value = "" Then

to this instead....

If TypeName(Ctrl) = "TextBox" Then
If Ctrl.Value = "" Then
<Code
End If
End If

otherwise it will "error out" when it comes across a control on the UserForm
that does not have a Value property, such as a CommandButton or Label. (VB
does not have short-circuit evaluation of logical statements, so both
conditions are tested even if the first condition is False.)

By the way, another way to test if the control is a TextBox is like this...

If TypeOf Ctrl Is TextBox Then

I like this way better myself because no part of it is case-sensitive (like
the TypeName property test is).

Rick

By the way, another way to test if the control is a TextBox is like
this...

If TypeOf Ctrl Is TextBox Then

Since Excel has a textbox object and the object on the userform
is not that object, it might be better to qualify

If TypeOf Ctrl Is Msforms.TextBox Then

Good point! Thanks for following up on that for me.

Rick