Locking Text Boxes in a Form using VBA

  • Thread starter Thread starter Jack Gillis
  • Start date Start date
J

Jack Gillis

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.
 
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.
 
Back
Top