Validate userform controls 1

[MikeAuz1979]

Hi, Using a userform in excel 2000, I want to check that the mandatory fields (With a Tag property of 'Pg1') are not blank/empty before proceding.

The code I'm using is below and all works well except for ctrl.text = "", any ideas how to check if the controls text is empty or not?

Thanks for any help
Mike

----Code Start----
dim ctrl as control
dim ValidMsg as string

For Each ctrl In frmMain.Controls
If ctrl.Tag = "Pg1" And ctrl.text = "" Then
ValidMsg = ValidMsg & vbCrLf & Mid(ctrl.Name, 4, Len(ctrl.Name) - 1)
End If
Next
If Not ValidMsg = "" Then
MsgBox "The following mandatory fields were not completed" & ValidMsg, vbCritical, "Validation Error"
Exit Sub
End If
----Code End----

 

[PHV]

If ctrl.Tag = "Pg1" And Trim(ctrl.Value & "") = "" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MikeAuz1979]

Thanks for answering PHV but when I try that I get the error that the object doesn't support property or method (.value is not something that comes up either when I type in ctrl. and hit ctrl and space)

any other ideas?

 

[PHV]

And what about this ?
For Each ctrl In frmMain.Controls
If ctrl.Tag = "Pg1" Then
If Trim(ctrl.Value & "") = "" Then
ValidMsg = ValidMsg & vbCrLf & Mid(ctrl.Name, 4, Len(ctrl.Name) - 1)
End If
End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MikeAuz1979]

Bingo!, love your work

 

[fumei]

If ctrl.Tag = "Pg1" Then
If Trim(ctrl.Value & "") = "" Then

If ctrl.Tag = "Pg1" And Trim(ctrl.Value & "") = "" Then

Hi PH. I have this situation before, and made it work the exact same way. By putting it into two If statements.

Trim(ctrl.Value & "") = "" - copied from the And
Trim(ctrl.Value & "") = "" - copied from the extra If

They are the same. Can you explain why it does not work? Why does the And not work?

Logically if the first is true, AND the second is true...it should work, yes? But it doesn't. It can't really be that .Value is not supported, as .Value DOES work for the second (two If instruction).

That is how the two If statements do it. If the first is true, then If the second is true....then.

Whenever I have come across this, the two If instructions is how i make it work. But I sure would like to understand WHY the And does not work.

faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

For me the problem is that VBA evaluates all the expressions of an If instruction before trying to make sense with the boolean operators.
Thus when ctrl is, say, a Label, VBA chokes on the non existent Value property, even if it doesn't matter as the Tag property <> "Pg1" ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

I am not following that. How can it be non-existent here (in bold):

If ctrl.Tag = "Pg1" And Trim([b]ctrl.Value[/b] & "") = "" Then

but existent here (in bold):

If ctrl.Tag = "Pg1" Then
        If Trim([b]ctrl.Value[/b] & "") = "" Then

are they not the same thing?

faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

The second If is evaluated only when .Tag = "Pg1"

Assumption: you'll not assign this Tag value to a Label ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

OK. True, but so what? I am still not getting it. I must be very dumb today.

The error is a non-existing type error (I think). I still do not understand how it can be non-existing in one place, and existing at another.

IF xxxxx And yyyyy causes the THEN to execute if both are True. Yes? Both xxxxx and yyyyyyy have to be valid expression. Yes? What makes ctrl.Value NOT a valid expression within an If...And..., but a valid expression in its own IF?

I would think that ctrl.Value is either a valid expression, or it is not.

So sure, in:
If ctrl.Tag = "Pg1" Then
If Trim(ctrl.Value & "") = "" Then

ctrl.Value is only validated if ctrl.Tag = "Pg1". Whether is validated as True, or False, does not matter. The validation process works.

and it IS validated (or rather is attempted) in:

If ctrl.Tag = "Pg1" And Trim(ctrl.Value & "") = ""

But why does the validation in the second example fail? The validation process does NOT work. Again, is it not the same "thing" being validated?

With: If ctrl.Tag = "Pg1" And Trim(ctrl.Value & "") = ""

Does not VBA go: check ctrl.Tag, then check ctrl.Value? And if both are True, proceed to the Then?

Even if it checks both of them at the same time (through some magic multi-processing), so what? If they are both valid expressions, then the only results are:

A = True and B = True....proceed to Then instructions
A = True and B = False...end of story
A = False and B = True...end of story
A = False and B = False...end of story

Why does the use of the Boolean AND make that second expression (valid with its own IF) not valid, or rather not even capable of being validated?

I don't want to take up too much of your time. I could very well be simply stupid.



faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

When ctrl is, say, a Label then the ctrl.Value evaluation is illegal as a label has no Value property (but a Caption).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

I still do not understand how it can be non-existing in one place, and existing at another
If A And B Then
A and B are ALWAYS evaluated (even when the context make the evaluation of B illegal)

If A Then
If B Then
B is ONLY evaluated when A is true (and thus make the evaluation of B legal)

Still not convinced, Gerry, that the two methods are different ?

 

[fumei]

Oh I always understood that the methods are different.

When ctrl is, say, a Label then the ctrl.Value evaluation is illegal as a label has no Value property

If there is no Value property...ummmm....and thus is illegal, I can see that the double IF allows the evaluation. So the evaluation is legal, but...but....but...if there is no Value property won't that still cause an error??? Apparently not.

And where did the Label thing come in, anyway? It was not mentioned as a Label. Considering it seems to be a validation of user input, I would think it is NOT a Label.

But anyway, enough of this. I have a headache. I am going to lie down now. Thanks PH for your patience.

faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

And where did the Label thing come in, anyway? It was not mentioned as a Label
The point at issue is that we test the whole Controls collection (For Each ctrl In frmMain.Controls) and thus ctrl may be an object that doesn't support the .Value property or method.
This is why a double If is safer than an And.
Still headache ?