Cannot Lock Text Boxes-Excel 2002

[celeste]

I have a spreadsheet with several text boxes that I do not want users t
edit. The text boxes are locked and the worksheet is protected.
However, users can still enter data into the locked text boxes. Th
only remedy that I have found is to disable the text boxes, but I d
not like the grayed font that is displayed when a box is disabled.

Any help is appreciated,
Celest

 

[Tom Ogilvy]

have you protected the objects on the worksheet? sheet protection has three
areas that can be protected.

 

[Doug Glancy]

Celeste,

Did you set the LockedText property along with the locked? For example:

Sub test()

With ActiveSheet.TextBoxes("Text Box 1")
.Locked = True
.LockedText = True
End With

End Sub

Note that the LockedText property is not available if you are referring to
the textbox as a member of Shapes, only when you access it through the
TextBoxes collection, as above.

hth,

Doug

 

[celeste]

Doug,
I tried your code and got "Object does not support this property o
method"

Celest

 

[Doug Glancy]

Celeste,

I only get that message if I'm referring to it as a Shape. When I change it
to TextBoxes, it works.

With ActiveSheet.TextBoxes("Text Box 1")

If you're still getting the message, I don't know what to do, as I can't
duplicate, and I am using XP 2002.

hth,

Doug

 

[celeste]

The text boxes that I am using are not drawing objects. They are activ
X objects. I think that's why that code that Doug suggested does no
work.

Celest

 

[Doug Glancy]

Celeste,

Ah ha. Yes, it looks to me like locking a textbox from the Controls Toolbox
means you can't manipulate it even in design mode. I don't see any way of
locking the actual text in it.

If you want to use something from the Controls Toolbox and not have it
changed by a user, would a label work?

Doug

 

[Tom Ogilvy]

Sounds like you are using a textbox from the control toolbox toolbar.
Recommend you use a label and change the SpecialEffect property to
fmSpecialEffectSunken (2). This makes the label look just like a textbox,
the text will be bright and it can't be edited.

 

[celeste]

A label works perfectly.

Thanks Tom and Doug!

Celest