sheet protection in excel with editable range

Hi, I've created a script to enumerate critical security groups (Domain
Admins, Enterprise Admins, etc etc) in Active Directory for manager
review on a regular basis. Since this is a control we're putting in
place for SOX compliance, one of the specifications was that the data
in the report be unalterable.

I was able to get protection working but I've got a new feature request
for a editable "comments" column for the managers to use during their
review. I can't seem to get the Sheet.Protection.AllowEditRange.Add
functionality to work.

I get the following error:


Microsoft VBScript runtime error: Object doesn't support this property
or method: 'sheet.Protection.AllowEditRange'

Can anyone help?

Code:

FOR EACH sheet IN myworkbook.sheets

sheet.activate

SET objRange = sheet.columns("F:F")

Sheet.Protection.AllowEditRange.Add objRange

'initially tried the following instead of objRange,
'cc from macro and gives a compilation error
' Title:="Range1", Range:=sheet.Columns("F:F")

'this works by itself but not with any other arguments

Sheet.Protect strmypassword

NEXT

Jim
Mon Sep 12 13:37:25 CDT 2005

mdowdy,
Never used it, but the following ought to come closer...

Sheet.Protection.AllowEditRanges.Add Range:=objRange

Note the "s" that was added. Also, you have to provide the name
of the argument or position it correctly...Title, Range, Password.
I don't know if the other arguments are optional or required.
The worksheet should be unprotected.
Jim Cone
San Francisco, USA


<mdowdy@spss.com>
wrote in message
news:1126545618.280827.33880@g44g2000cwa.googlegroups.com
Hi, I've created a script to enumerate critical security groups (Domain
Admins, Enterprise Admins, etc etc) in Active Directory for manager
review on a regular basis. Since this is a control we're putting in
place for SOX compliance, one of the specifications was that the data
in the report be unalterable.

I was able to get protection working but I've got a new feature request
for a editable "comments" column for the managers to use during their
review. I can't seem to get the Sheet.Protection.AllowEditRange.Add
functionality to work.

I get the following error:


Microsoft VBScript runtime error: Object doesn't support this property
or method: 'sheet.Protection.AllowEditRange'
Can anyone help?
Code:

FOR EACH sheet IN myworkbook.sheets
sheet.activate
SET objRange = sheet.columns("F:F")
Sheet.Protection.AllowEditRange.Add objRange
'initially tried the following instead of objRange,
'cc from macro and gives a compilation error
' Title:="Range1", Range:=sheet.Columns("F:F")
'this works by itself but not with any other arguments
Sheet.Protect strmypassword
NEXT

mdowdy
Mon Sep 12 16:20:19 CDT 2005

Thanks Jim - while that didn't exactly do it, I finally did manage to
figure it out. Give it the range name, and the range, but don't try to
qualify them.


Dim strmypassword


Sheet.Protection.AllowEditRanges.Add "Comments", sheet.Columns("F:F")
Sheet.Protect strmypassword