Protect Sheet but enable cell ranges giving Error 1004 Unable to set
 

Hi,
I am using the following code to disable a worksheet so
that people cannot tamper with data on it as it is used
in formulas else where on the sheet but allow them to
enter data in allocated cells which is then worked on to
give a result. Using the following code gives me an error
1004 Unable to sey the locked property of the Range
Classes , the help file doen't seem to indicate the
reason.

Option Explicit
Sub auto_open()

Worksheets(1).Range("b3:b6").Locked = False 'data
entry cells
Worksheets(1).Range("f6:f8").Locked = False 'change
variables allow cells

Worksheets(1).Protect

Regards

End Sub

Protect Sheet but enable cell ranges giving Error 1004 Unable to set
 

Hi William,

This is an error that I would expect to occur if the worksheet was
already protected when your application started up. Modify your code to
unprotect the worksheet prior to making the changes and the protect it again
afterwards, like so;

Sub auto_open()
Worksheets(1).Unprotect
Worksheets(1).Range("b3:b6").Locked = False 'data entry cells
Worksheets(1).Range("f6:f8").Locked = False 'change variables allow
cells
Worksheets(1).Protect
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"William" wrote in message
...
Hi,
I am using the following code to disable a worksheet so
that people cannot tamper with data on it as it is used
in formulas else where on the sheet but allow them to
enter data in allocated cells which is then worked on to
give a result. Using the following code gives me an error
1004 Unable to sey the locked property of the Range
Classes , the help file doen't seem to indicate the
reason.

Option Explicit
Sub auto_open()

Worksheets(1).Range("b3:b6").Locked = False 'data
entry cells
Worksheets(1).Range("f6:f8").Locked = False 'change
variables allow cells

Worksheets(1).Protect

Regards

End Sub

Protect Sheet but enable cell ranges giving Error 1004 Unable to set
 

Try introducing a new first line of code to unprotect the sheet.....

Worksheets(1).Unprotect
Worksheets(1).Range("b3:b6").Locked = False
Worksheets(1).Range("f6:f8").Locked = False
Worksheets(1).Protect

--

XL2002
Regards

William



"William" wrote in message
...
| Hi,
| I am using the following code to disable a worksheet so
| that people cannot tamper with data on it as it is used
| in formulas else where on the sheet but allow them to
| enter data in allocated cells which is then worked on to
| give a result. Using the following code gives me an error
| 1004 Unable to sey the locked property of the Range
| Classes , the help file doen't seem to indicate the
| reason.
|
| Option Explicit
| Sub auto_open()
|
| Worksheets(1).Range("b3:b6").Locked = False 'data
| entry cells
| Worksheets(1).Range("f6:f8").Locked = False 'change
| variables allow cells
|
| Worksheets(1).Protect
|
| Regards
|
| End Sub
|

Protect Sheet but enable cell ranges giving Error 1004 Unable to set
 

Thanks Gentlemen,
Very Much appreciated.
Regards
William
-----Original Message-----
Hi William,

This is an error that I would expect to occur if the
worksheet was
already protected when your application started up.
Modify your code to
unprotect the worksheet prior to making the changes and
the protect it again
afterwards, like so;

Sub auto_open()
Worksheets(1).Unprotect
Worksheets(1).Range("b3:b6").Locked = False 'data
entry cells
Worksheets(1).Range("f6:f8").Locked = False 'change
variables allow
cells
Worksheets(1).Protect
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"William" wrote in
message
...
Hi,
I am using the following code to disable a worksheet so
that people cannot tamper with data on it as it is used
in formulas else where on the sheet but allow them to
enter data in allocated cells which is then worked on
to
give a result. Using the following code gives me an
error
1004 Unable to sey the locked property of the Range
Classes , the help file doen't seem to indicate the
reason.

Option Explicit
Sub auto_open()

Worksheets(1).Range("b3:b6").Locked = False 'data
entry cells
Worksheets(1).Range("f6:f8").Locked = False 'change
variables allow cells

Worksheets(1).Protect

Regards

End Sub



.