Worksheet Range Protection

jscott

New Member
Joined
Mar 6, 2002
Messages
5
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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:

<pre>
objWS.Range("$A$2:$D$65536").Locked = True
objWS.Protect PassWord:="", DrawingObjects:=True, Contents:=True, Scenarios:=True
</pre>

This code would be instead of your two protect lines.

HTH
 
Upvote 0
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?
 
Upvote 0
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:

<pre>
objWS.UsedRange.Locked = False
</pre>

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:

<pre>

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

</pre>

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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Yup,

you're using 2002.

Here's a link that'll be useful to you, I think it answers your question:<a href> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xlproAllowEditRanges.asp></a>

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<pre>
objWS.Protection.AllowEditRanges.Add Title:="Classified", Range:=Range("$A$2:$D$65536"), Password:=""</pre>

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

_________________<font color = green> 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
 
Upvote 0
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!
 
Upvote 0
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. :wink:
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-03-07 09:55
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top