Unable to Lock a Range
 

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.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004

Unable to Lock a Range
 

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



Stuart wrote:

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.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004

--

Dave Peterson

Unable to Lock a Range
 

On Sat, 10 Jul 2004 12:55:18 +0100, "Stuart"
wrote:

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.)

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

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Unable to Lock a Range
 

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.

"Dave Peterson" wrote in message
...
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



Stuart wrote:

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.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004

--

Dave Peterson



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004

Unable to Lock a Range
 

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.

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
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.

"Dave Peterson" wrote in message
...
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



Stuart wrote:

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.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004

--

Dave Peterson



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004

Unable to Lock a Range
 

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 ViewToolbars
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004

Unable to Lock a Range
 

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

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
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 ViewToolbars
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004

Unable to Lock a Range
 

Removes them now, many thanks.

Regards.

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
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 ViewToolbars
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004