Worksheet Range Protection

[jscott]

I'm trying to protect a certain range of cells in a worksheet from within Access.

My code is below:

Dim objExcel As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Worksheet
Dim intColumn As Integer
Dim intRow As Integer
Dim intRecords As Integer

Set objExcel = Excel.Application
objWS.Unprotect
objWS.Protection.AllowEditRanges.Add Title:="Classified", Range:=Range("$A$2:$D$65536"), Password:=""

objWS.Columns.AutoFit
objWS.Protect
objWB.SaveAs strFileName

I get a method or data member not found on the objws.protection line.

I am referencing the Excel 8.0 object library.

Can anyone help?? I'd really appreciate it. I'm new to the Excel object model!

Thanks!
Jeanine Scott

[Mark O'Brien]

On 2002-03-07 08:16, jscott wrote:
I'm trying to protect a certain range of cells in a worksheet from within Access.

My code is below:

Dim objExcel As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Worksheet
Dim intColumn As Integer
Dim intRow As Integer
Dim intRecords As Integer

Set objExcel = Excel.Application
objWS.Unprotect
objWS.Protection.AllowEditRanges.Add Title:="Classified", Range:=Range("$A$2:$D$65536"), Password:=""

objWS.Columns.AutoFit
objWS.Protect
objWB.SaveAs strFileName

I get a method or data member not found on the objws.protection line.

I am referencing the Excel 8.0 object library.

Can anyone help?? I'd really appreciate it. I'm new to the Excel object model!

Thanks!
Jeanine Scott

In Excel, to protect a specific range of cells, you first need to "lock" the cells and then protect the worksheet.

So something like this should work in your case:

    objWS.Range("$A$2:$D$65536").Locked = True

    objWS.Protect PassWord:="", DrawingObjects:=True, Contents:=True, Scenarios:=True

This code would be instead of your two protect lines.

HTH

[jscott]

Thanks, Mark. I did try this - however what appears to happen is that the entire worksheet becomes protected.

I only want a certain range protected which is why I went in the AllowEditRanges direction. When I take the protection off the locked cells are editble.

What am I doing wrong?

[Mark O'Brien]

On 2002-03-07 09:03, Mark O'Brien wrote:
Yeah, my bad. MS locks all of the cells be default.

Try this before locking the range:

objWS.UsedRange.Locked = False

This will unlock any cells that have data in them. If you're not constantly adding data then you can do this each time. If you are then I would suggest something like this:

objWS.Range("A1:IV65536").Locked = False

To unlock all of the cells on the sheet the first time you run.

I'm not sure of the difference in runtime for these two pieces of code, but I would stick to the first one if I had a choice.


Also, the only way to protect individual cells is to lock them and then protect the sheet. So if the sheet isn't protected, the locked cells aren't protected. As far as I know, this is a limitation in Excel.

In your code, you might just have to protect the sheet again at the end.

hmmm...
I did get this to work exactly the way I wanted it to in Excel by utilizing the "Allow Users to Edit Ranges" option on the Protection menu, then protecting the sheet. It protects my range while at the same time allowing the other cells to be edited.

Can you not do this via code?

[jscott]

Sorry! forgot to put my username on the post...not trying to be anonymous

On 2002-03-07 09:16, Anonymous wrote:
hmmm...
I did get this to work exactly the way I wanted it to in Excel by utilizing the "Allow Users to Edit Ranges" option on the Protection menu, then protecting the sheet. It protects my range while at the same time allowing the other cells to be edited.

Can you not do this via code?

[Mark O'Brien]

Are you using Excel XP?

I've never used that and this could be a new feature in it. If it is, then it's long overdue.

To find out what the VBA is, just use the macro recorder to record your actions and look at the code generated.

[Mark O'Brien]

Yup,

you're using 2002.

Here's a link that'll be useful to you, I think it answers your question:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xlproAllowEditRanges.asp>

Actually, no it doesn't solve your problem. What would solve your problem is going back to your original code then removing the unprotect line from your code. If you unprotect your worksheet, then all users can edit the sheet as they see fit. So the error generated is basically telling you that this line

objWS.Protection.AllowEditRanges.Add Title:="Classified", Range:=Range("$A$2:$D$65536"), Password:="" 

is nonsense, because there's no protection on the sheet once it's unprotected, hence the method fails.

I hope this solves the problem. I really, really do.

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-03-07 09:40 ]

[ This Message was edited by: Mark O'Brien on 2002-03-07 09:47 ]

[jscott]

Ahh! that explains it. I have to use the Excel 8.0 object library since my clients have Excel97. However I have Excel 2002 on my machine - so of course the 8.0 library won't recognize the 2002 properties. Well, that sucks - to put it bluntly.

Guess it's just going to have to be "one of those things"

Thanks for your help!

[Mark O'Brien]

On 2002-03-07 09:45, jscott wrote:
Ahh! that explains it. I have to use the Excel 8.0 object library since my clients have Excel97. However I have Excel 2002 on my machine - so of course the 8.0 library won't recognize the 2002 properties. Well, that sucks - to put it bluntly.

Guess it's just going to have to be "one of those things"

Thanks for your help!

Tell your clients to spend a couple of hundred (bucks, quid, yen) whatever on XL2002 if they really want it. I'm guessing this is a small percentage of the cost of any work they're getting you to do.

I've done this before going from XL97 and XL2000 when I hit problems with new features.

Although, they sound pretty cheap if they're still on 97.
_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-03-07 09:55 ]