Unable to Lock a Range

[Stuart]

Here's the code:

With ActiveWorkbook.Sheets("Contract Master Order")
.Range("A1:N70").Locked = True
.Protect Password:="SGB"
.EnableSelection = xlUnlockedCells
End With

The Locked statement results in Error 1004,
Unable to set the Locked property of the Range class.

I've some embedded ActiveX textboxes in the range in question
........is that the problem?

If so, can I remove the controls (but retain the user's text) and
then protect the sheet and book, please?

Regards.

 

[Dave Peterson]

You sure that the worksheet was unprotected when you started?

Maybe checking first would be beneficial.

Option Explicit
Sub testme()

With ActiveWorkbook.Sheets("Contract Master Order")

If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
'it's protected
.Unprotect Password:="SGB"
End If

.Range("A1:N70").Locked = True
.Protect Password:="SGB"
.EnableSelection = xlUnlockedCells

End With
End Sub

 

[Hank Scorpio]

With ActiveWorkbook.Sheets("Contract Master Order")
.Range("A1:N70").Locked = True
.Protect Password:="SGB"
.EnableSelection = xlUnlockedCells
End With

The Locked statement results in Error 1004,
Unable to set the Locked property of the Range class.

I've some embedded ActiveX textboxes in the range in question
.......is that the problem?

I doubt it. I don't seem to be encountering that problem in a test
workbook with embedded ActiveX controls, and I haven't seen it before.

This is probably a rather obvious thing to ask, but what the hey...
are you sure that the sheet is unprotected at the time that you
execute the

.Range("A1:N70").Locked = True

line? If it's not (perhaps from having run the procedure previously),
you'll get a 1004 error. If that's all it is, add an extra line
unprotecting the sheet before you change the Locked property. (Do an
IF test to make sure that the sheet is in fact protected before
executing the Unprotect one.)

 

[Stuart]

Many thanks to you both.

You're both right. It was protected.
I'd set a breakpoint and I then used unprotect code in
the Immediate window before continuing on to step
through the Locked code.
Because it kept throwing the same error, I was looking
for some other problem.

I'd still be grateful for instruction regarding removing
the ActiveX textboxes (but preserving the text entries)
before I save the book, please.

Regards and thanks.

 

[Tom Ogilvy]

for each oleObj in ActiveWorkbook.Sheets( _
"Contract Master Order").OleObjects
if typeof oleObj.Object is MSForms.TextBox then
oleObj.Delete
end if
Next

Assumes the textboxes are linked to cells. Deleting the textbox shouldn't
affect the value in the cell.

 

[Stuart]

Many thanks.

Dimmed oleObj As OleObject.
Code then removed the textboxes and the entries.

I set the textboxes up as follows:
select a cell in the sheet
click View>Toolbars
select Control Toolbox
select Textbox
click on the same cell and drag to expand the range
right click the Control and choose Properties
enable Tab, Return and WrapText
(nb: couldn't see how to make text align to Top)
exit Design mode

This must be different to how you envisaged I had
created them?

Regards and Thanks.

 

[Tom Ogilvy]

Dim oleObj as OleObject
Dim rng as Range
for each oleObj in ActiveWorkbook.Sheets( _
"Contract Master Order").OleObjects
if typeof oleObj.Object is MSForms.TextBox then
set rng = oleObj.TopLeftCell
rng.Value = oleObj.Object.Value
oleObj.Delete
end if
Next

 

[Stuart]

Removes them now, many thanks.

Regards.

Dim oleObj as OleObject
Dim rng as Range
for each oleObj in ActiveWorkbook.Sheets( _
"Contract Master Order").OleObjects
if typeof oleObj.Object is MSForms.TextBox then
set rng = oleObj.TopLeftCell
rng.Value = oleObj.Object.Value
oleObj.Delete
end if
Next