Prev: Filter Table based on Cell Value
Next: formulas containing constants
From: Memphis on 25 Mar 2010 16:35
Hello,
I have a user form with several text, combo and checkboxes, as well as
command buttons.
I would like for the user to check a check box and lock all txt,cmb and chk
boxes in the form once they are entering the data, but I want to keep the cmd
buttons enabled, so I do not need to lock the whole userform.

I would like to see if there is a way to do this in a "batch", for example
If chkbox1= True then "All txtboxes".Locked=True
I would hate to name every single one.

Thank you

From: B Lynn B on 25 Mar 2010 19:36
You can loop through the controls collection on your form. If you've
followed a good naming convention (i.e. command buttons are named something
like cmdName) then snippet below would work:

Sub LockControls()

Dim myCtrl as Control

For Each myCtrl in me.Controls
If Right(myCtrl.Name, 3) <> "cmd" Then myCtrl.Locked = True
Next myCtrl

End Sub

"Memphis" wrote:

> Hello,
> I have a user form with several text, combo and checkboxes, as well as
> command buttons.
> I would like for the user to check a check box and lock all txt,cmb and chk
> boxes in the form once they are entering the data, but I want to keep the cmd
> buttons enabled, so I do not need to lock the whole userform.
>
> I would like to see if there is a way to do this in a "batch", for example
> If chkbox1= True then "All txtboxes".Locked=True
> I would hate to name every single one.
>
> Thank you
>
From: B Lynn B on 25 Mar 2010 19:39
Ahem. Uh, yes, that should have been the "other Right"...

If Left(myCtrl.Name, 3) <> "cmd" Then myCtrl.Locked = True

"B Lynn B" wrote:

> You can loop through the controls collection on your form. If you've
> followed a good naming convention (i.e. command buttons are named something
> like cmdName) then snippet below would work:
>
> Sub LockControls()
>
> Dim myCtrl as Control
>
> For Each myCtrl in me.Controls
> If Right(myCtrl.Name, 3) <> "cmd" Then myCtrl.Locked = True
> Next myCtrl
>
> End Sub
>
> "Memphis" wrote:
>
> > Hello,
> > I have a user form with several text, combo and checkboxes, as well as
> > command buttons.
> > I would like for the user to check a check box and lock all txt,cmb and chk
> > boxes in the form once they are entering the data, but I want to keep the cmd
> > buttons enabled, so I do not need to lock the whole userform.
> >
> > I would like to see if there is a way to do this in a "batch", for example
> > If chkbox1= True then "All txtboxes".Locked=True
> > I would hate to name every single one.
> >
> > Thank you
> >
 | 
Pages: 1
Prev: Filter Table based on Cell Value
Next: formulas containing constants