Hi All,

I have a form with approx 20 fields made up of comboboxes & textboxes. I
have renamed them so that the code is easier to read. ie they are no longer
ComboBox1, ComboBox2 etc.

Does anybody have any code which will check that none of the fields are
blank ?

I can write the code to check each field individually but it seems to me to
be a very inelegant solution.

Also,

Is it possible to restrict entries (ie only Alpha chararcters allowed or
only numerical characters allowed) and the number of characters ?

All help gratefully appreciated

Regards

Michael Beckinsale

for each ctrl in Userform1.Controls
if typeof ctrl is MSForms.TextBox then
if ctrl.Value < "" then

end if
if typeof ctrl is MSForms.ComboBox then
if ctrl.Value < "" then

end if
end if
Next

Assuming you mean textboxes (comboboxes can be restricted to the items in
the list)

This example at John Walkenbach's site shows how to handle multiple controls
in a class module:

http://www.j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

you would put code in there to do the validation. Even though the above
code is for commandbuttons, it can be used with other msforms controls as
well.

--
Regards,
Tom Ogilvy



"Michael Beckinsale" wrote in message
...
Hi All,

I have a form with approx 20 fields made up of comboboxes & textboxes. I
have renamed them so that the code is easier to read. ie they are no
longer
ComboBox1, ComboBox2 etc.

Does anybody have any code which will check that none of the fields are
blank ?

I can write the code to check each field individually but it seems to me
to
be a very inelegant solution.

Also,

Is it possible to restrict entries (ie only Alpha chararcters allowed or
only numerical characters allowed) and the number of characters ?

All help gratefully appreciated

Regards

Michael Beckinsale

Tom,

Thats great. Exactly what l need. Thank you very much.

Regards

Michael.

"Tom Ogilvy" wrote in message
...
for each ctrl in Userform1.Controls
if typeof ctrl is MSForms.TextBox then
if ctrl.Value < "" then

end if
if typeof ctrl is MSForms.ComboBox then
if ctrl.Value < "" then

end if
end if
Next

Assuming you mean textboxes (comboboxes can be restricted to the items in
the list)

This example at John Walkenbach's site shows how to handle multiple
controls
in a class module:

http://www.j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

you would put code in there to do the validation. Even though the above
code is for commandbuttons, it can be used with other msforms controls as
well.

--
Regards,
Tom Ogilvy



"Michael Beckinsale" wrote in message
...
Hi All,

I have a form with approx 20 fields made up of comboboxes & textboxes. I
have renamed them so that the code is easier to read. ie they are no
longer
ComboBox1, ComboBox2 etc.

Does anybody have any code which will check that none of the fields are
blank ?

I can write the code to check each field individually but it seems to me
to
be a very inelegant solution.

Also,

Is it possible to restrict entries (ie only Alpha chararcters allowed or
only numerical characters allowed) and the number of characters ?

All help gratefully appreciated

Regards

Michael Beckinsale