Locking Text Boxes in a Form using VBA

I have designed a form with 26 textboxes. Each is name in the form of
txtName, txtAmount and so forth.

I would like to lock these boxes with a few lines of code without having
to put a line in for each box. Maybe something in the order of

For each TextBox in Worksheet
.locked = true.

but I haven't been able to figure out how to do it. I found a 'For
Each' example in Help but it appears not to apply to text boxes.

I know I can do it with individual lines for each box but had much
rather use a looping method. Then, if I added some more text boxes I
wouldn't have to put additional lines in the code to lock each box.

Can anyone suggest a way to do this?

Thank you very much.

You could loop through the controls looking for textboxes:

Private Sub UserForm_Initialize()
Dim myCtrl As Control
For Each myCtrl In Me.Controls
If TypeOf myCtrl Is MSForms.TextBox Then
myCtrl.Object.Locked = True
End If
Next myCtrl
End Sub

Or you could loop through the controls looking for names that start with txt.

Private Sub UserForm_Initialize()
Dim myCtrl As Control
For Each myCtrl In Me.Controls
If LCase(myCtrl.Name) Like "txt*" Then
myCtrl.Object.Locked = True
End If
Next myCtrl
End Sub

And did you mean a UserForm when you wrote Form or did you mean a worksheet made
up to look like a form?

If you meant a worksheet, what textboxes did you use--from the Drawing toolbar
or from the Control toolbox toolbar.

Jack Gillis wrote:
>
> I have designed a form with 26 textboxes. Each is name in the form of
> txtName, txtAmount and so forth.
>
> I would like to lock these boxes with a few lines of code without having
> to put a line in for each box. Maybe something in the order of
>
> For each TextBox in Worksheet
> .locked = true.
>
> but I haven't been able to figure out how to do it. I found a 'For
> Each' example in Help but it appears not to apply to text boxes.
>
> I know I can do it with individual lines for each box but had much
> rather use a looping method. Then, if I added some more text boxes I
> wouldn't have to put additional lines in the code to lock each box.
>
> Can anyone suggest a way to do this?
>
> Thank you very much.

--

Dave Peterson

Yes, I meant User Form. Sorry about the ambiguity.

I think I will choose the first method for it seems more specific and I
want
all textboxes set to 'locked.'

Thank you very much for the suggestion.

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> You could loop through the controls looking for textboxes:
>
> Private Sub UserForm_Initialize()
> Dim myCtrl As Control
> For Each myCtrl In Me.Controls
> If TypeOf myCtrl Is MSForms.TextBox Then
> myCtrl.Object.Locked = True
> End If
> Next myCtrl
> End Sub
>
> Or you could loop through the controls looking for names that start
> with txt.
>
> Private Sub UserForm_Initialize()
> Dim myCtrl As Control
> For Each myCtrl In Me.Controls
> If LCase(myCtrl.Name) Like "txt*" Then
> myCtrl.Object.Locked = True
> End If
> Next myCtrl
> End Sub
>
> And did you mean a UserForm when you wrote Form or did you mean a
> worksheet made
> up to look like a form?
>
> If you meant a worksheet, what textboxes did you use--from the Drawing
> toolbar
> or from the Control toolbox toolbar.
>
> Jack Gillis wrote:
>>
>> I have designed a form with 26 textboxes. Each is name in the form
>> of
>> txtName, txtAmount and so forth.
>>
>> I would like to lock these boxes with a few lines of code without
>> having
>> to put a line in for each box. Maybe something in the order of
>>
>> For each TextBox in Worksheet
>> .locked = true.
>>
>> but I haven't been able to figure out how to do it. I found a 'For
>> Each' example in Help but it appears not to apply to text boxes.
>>
>> I know I can do it with individual lines for each box but had much
>> rather use a looping method. Then, if I added some more text boxes
>> I
>> wouldn't have to put additional lines in the code to lock each box.
>>
>> Can anyone suggest a way to do this?
>>
>> Thank you very much.
>
> --
>
> Dave Peterson