Worksheet protection VBA

[James]

Hi,

I have a worksheet that needs to be protected from changes once the report
is run. The sheet has three levels of protection at the moment.
#1- The entire sheet is locked from changes, except 4 columns.
#2- Three columns were set with a password for a small group to edit
comments.
#3- One column is set with no password for anyone to edit.

Is there a way for a macro to delete permission #2? The idea being, once
the report is run, it is locked. Those with permission #2 can edit the
specified fields as needed, then they click a button which deletes sheet
protection #2 preventing any future changes. Only permission #3 remains
allowing anyone to edit one column in the entire sheet.

Thanks!

 

[Tom Hutchins]

Here is one way...

I assume you are using some event code to prompt the users for the password
to unprotect the three columns (I used Worksheet_SelectionChange). If your
macro to stop permission #2 puts a value in a particular (out of the way)
cell, then your event code can check that cell and leave the sheet protected
when is sees a value in that cell. The following code accomplishes this; it
is in the code page for Sheet1 in my example.

Const Pwd = "aaa"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ret As Variant
If Len(ActiveSheet.Range("AA1").Value) > 0 Then Exit Sub
If Intersect(Target, Columns("H:J")) Is Nothing Then
ActiveSheet.Protect Password:=Pwd
Else
ret = InputBox("Enter password")
If ret <> Pwd Then Exit Sub
ActiveSheet.Unprotect Password:=ret
End If
End Sub

Sub FinalizeSht()
ActiveSheet.Unprotect Password:=Pwd
ActiveSheet.Range("AA1").Value = "X"
ActiveSheet.Protect Password:=Pwd
End Sub

In this example, the 3 columns are H:J, the password is "aaa", and the
designated cell is AA1. While AA1 is empty, users are prompted for the
password when they select any cell in columns H:J. After the FinalizeSht
macro is run (could be attached to a button), the Worksheet_SelectionChange
event quits without doing anything.
The designated cell could be on another sheet if desired, or it could be
hidden as well as locked.

Hope this helps,

Hutch

 

[James]

This seems far more complex then I imagined. I actually am using no code at
the moment to set my security up. Just "Tools/Protection/Protect Sheet" and
"Tools/Protection/Allow Users to Edit Range". Is there a simple code that
would delete one of the Ranges that was set up in "Tools/Protection/Allow
Users to Edit Range" ?

That should cause the three columns to revert security back to the defaut
sheet lockout, correct?

 

[Tom Hutchins]

Okay...sorry for the misunderstanding and for the delay in this reply. Here
is some code that should do what you want and be easy to implement:

Sub SetProtection()
Dim x As Integer
On Error Resume Next
'Unprotect the sheet
ActiveSheet.Unprotect Password:="aaa"
'Remove all the AllowEditRanges
For x = 1 To ActiveSheet.Protection.AllowEditRanges.Count
ActiveSheet.Protection.AllowEditRanges(1).Delete
DoEvents
Next x
'Now add back the no-password AllowEditRange
ActiveSheet.Protection.AllowEditRanges.Add _
Title:="Range1", Range:=Columns("K:K")
'Re-protect the sheet.
ActiveSheet.Protect Password:="aaa"
End Sub

This macro unprotects the sheet, removes all the permissions, adds back the
one with no password, and re-protects the sheet. You need to replace "aaa"
(two places) with the password to unprotect/protect the sheet and replace
"K:K" with the column which should have the no-password permission.

To implement this macro, right-click the sheet name tab on the sheet where
this code should operate. In the menu that appears, select View Code. The
Visual Basic Editor will open. In the big blank window, paste the code above.
Select File >> Close to quit the Editor and return to regular Excel.

You can run the macro by selecting Tools >> Macro >> Macros >> SetProtectionworksheet. If you do, make sure the button is not locked.

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hutch