Am I 'Dim'

[GrahamB]

I am using Excel 2000 and have created a workbook with 3 worksheets which I
have tried to protect with the code .



Private Sub Workbook_Open()

'This macro automatically protects the worksheets on opening, allows macros
to run, and allows limited formatting 'before going to the 'Date cell.



Sheets(1).Protect Password:="secret", UserInterfaceOnly:=True

Sheets(2).Protect Password:="secret", AllowInsertingRows:=True,
AllowDeletingRows:=True, AllowSorting:=True

Sheets(3).Protect Password:="secret", AllowInsertingRows:=True,
AllowSorting:=True



It accepts the first line but comes up with 'run-time 1004 error due to App.
or Obj. defined error' somewhere on the line starting 'Sheets(2)'. I'm
trying to teach myself VBA from a book so please be gentle with me if it's a
simple error. GrahamB

 

[Pete_UK]

I think that with Excel 2002 and later you are allowed more choices
when sheets are protected, but as you are using XL2k you can't make
use of these features. Perhaps your book is written for a later
version and so some of the code won't work.

Hope this helps.

Pete

 

[Mike]

GrahamB,

There is nothing wrong with any of the lines of code below, they all work
fine so something else in the macro is giving the problem. The only way I
could get an error with your code is in a workbook with only 2 sheets but
that error was subscript out of range. Perhaps you could post more your macro
and provide your excel version.

Mike

 

[Jim Cone]

From the Excel 2000 help file...
"Protects a chart or worksheet (Syntax 1) or
a workbook (Syntax 2) so that it cannot be modified."
'---
Syntax 1
..Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly)
'---
Syntax 2
..Protect(Password, Structure, Windows
'---

Note that there are no options to insert/delete rows.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"GrahamB" <[email protected]>
wrote in message
I am using Excel 2000 and have created a workbook with 3 worksheets which I
have tried to protect with the code .

Private Sub Workbook_Open()
'This macro automatically protects the worksheets on opening, allows macros
to run, and allows limited formatting 'before going to the 'Date cell.
Sheets(1).Protect Password:="secret", UserInterfaceOnly:=True
Sheets(2).Protect Password:="secret", AllowInsertingRows:=True,
AllowDeletingRows:=True, AllowSorting:=True
Sheets(3).Protect Password:="secret", AllowInsertingRows:=True,
AllowSorting:=True

It accepts the first line but comes up with 'run-time 1004 error due to App.
or Obj. defined error' somewhere on the line starting 'Sheets(2)'. I'm
trying to teach myself VBA from a book so please be gentle with me if it's a
simple error. GrahamB

 

[GrahamB]

I suspect you're all correct, maybe time to splash out on a later version -
at least the book is 2007 vintage !
But if I leave sheets 2 & 3 unprotected, can I use a formula in 'Data
Validation' to ensure some of the cells in Colunm A only are protected;
(therefore letting users Sort, Insert etc.)?