Using VBA to protect & unprotect cells

Posted by Tom on April 27, 2001 6:33 PM

This is sort of a followup to my previous question. I want to protect a range of cells, then unprotect them so I can create a new worksheet by copying and pasting the original, then re-protect the original worksheet and the new, copied worksheet. I'm trying to use this code:

Sub protect()
Range("A3:A12,D3:E12,J1:R13,W18")
Range("W18").Activate
Selection.Locked = True
ActiveSheet.protect Contents:=True
End Sub

Then I use the following to copy the worksheet and create a new one:

Sub AddNewSheet()
ActiveSheet.Unprotect
Cells.Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Call protect
End Sub

When the original protect routine is called for the first time, everything works fine. The unprotect command also works. But when the protect routine is called from within the AddNewSheet routine, nothing gets protected again. Thanks in advance for your help.

Tom

Posted by Dave Hawley on April 27, 2001 7:39 PM

Hi Tom

Try it this way.

Sub protect()
Range("A3:A12,D3:E12,J1:R13,W18").Locked = True
ActiveSheet.protect Contents:=True
End Sub

Sub AddNewSheet()
ActiveSheet.Unprotect
Cells.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Run "protect"
End Sub

Dave

OzGrid Business Applications

Posted by Tom on April 29, 2001 9:25 AM

Thanks for your help, Dave, but it didn't work. When I ran the Protect routine, the entire worksheet got protected, not just the cells in the Range command. Further, the cells in the newly added worksheet weren't protected and the cells in the original worksheet did not get re-protected. Do you have any more suggestions? Thanks very much.

Tom

Posted by Dave Hawley on April 29, 2001 7:33 PM

Hi Tom

A few things you need to know about how Excel applies protection.

When you apply protection you are applying the protection to the entire Worksheet.

There are three levels of Sheet protection in the user interface. They are:

DrawingObjects: Optional Variant. True to protect shapes. The default value is False.

Contents: Optional Variant. True to protect contents. For a chart, this protects the entire chart. For a worksheet, this protects the individual cells. The default value is True.

Scenarios: Optional Variant. True to protect scenarios. This argument is valid only for worksheets. The default value is True

There is a fouth level within the VBE, that is:

UserInterfaceOnly: Optional Variant. True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.

All cells are Locked by default.

Locking a cell has NO effect unless Sheet Protection is applied.

All cells that are Locked are fully protected when Sheet protection is applied and includes Contents.

I notice also you have used the word "protect" as the name of a Procedure. This is very close to using one of Excels reserved Key words, I would suggest stronly changing it.

So at the end of all that you may like to try:

Sub ProtectTheSheet()
Cells.Locked = False
Range("A3:A12,D3:E12,J1:R13,W18").Locked = True
ActiveSheet.protect
End Sub

Sub AddNewSheet()
ActiveSheet.Unprotect
Cells.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Run "ProtectTheSheet"
End Sub

Dave

OzGrid Business Applications