Printable View
I`m in need of a code to protect and un-protect 2 sheets in a workbook.In the Activity Sheet I want the sheet protected except for the cells highlighted Green.These cell ranges are always available for editing so no protection needed.
In the Progress sheet I want this whole sheet protected unless the Un-Protect button activated
I will use a button on each sheet so as I can Protect or Unprotect each sheet individually....can these 2 individual buttons have password requirements
Thanks
Ray
raybugge, why don't you use the native sheet protection method, with password? And you can use separate passwords for each sheet. And you can use the button on the review tab (Excel 2007+) to protect or un-protect the file. I do not think you need a macro for this. Unless you have more elaborate plans :)
By the way, to leave a few cells as not protected, all you need is to change the property of the cells to Unlocked (default is Locked). Then protect the sheets.
Hello Excel Fox
I was going to use the native sheet protection but there will more cells locked then I have shown.This is a early version of a planned data spreadsheet...would prefer a code with button options if possible...thanks...Ray
Try this. You'll need to paste this in the respective sheet module. Note the name of the command button. This has to be consistent with what's on your sheet. In your example above, it was CommandButton1
Code:Private Sub CommandButton1_Click()
Dim strPassword As String
Const strActualPassword As String = "ABCD"
strPassword = InputBox("Please enter the password", "Protect/Unprotect Sheet")
If strActualPassword = strPassword Then
If Me.CommandButton1.Caption = "PROTECT SHEET" Then
Me.CommandButton1.Caption = "UNPROTECT SHEET"
UnlockCells
Me.Protect Password:=strPassword
Else
Me.CommandButton1.Caption = "PROTECT SHEET"
Me.Unprotect Password:=strPassword
End If
Else
MsgBox "Invalid Password"
End If
End Sub
Sub UnlockCells()
Me.Range("B10:T10,B16:T18,B24:T28").Locked = False
End Sub
Hi Excel Fox
I placed the code into "Activity" sheet and it works very good...can the code for the Sheet "Progress" be separate to the code for Activity sheet...use the same caption "Protect / Un-protect" on a commandbutton but this button either locks the whole sheet or unlocks the whole sheet
The code for Activity sheet works independent of the code for Progress sheet...both sheets can be locked or unlocked at the same time,1 sheet locked while the other sheets is unlocked at the same time etc..etc..
Thanks
Ray
Use the same code above, and just remove the function that unlocks the cells. Again, you can use any different password for this too.
Code:
Private Sub CommandButton1_Click()
Dim strPassword As String
Const strActualPassword As String = "ABCD"
strPassword = InputBox("Please enter the password", "Protect/Unprotect Sheet")
If strActualPassword = strPassword Then
If Me.CommandButton1.Caption = "PROTECT SHEET" Then
Me.CommandButton1.Caption = "UNPROTECT SHEET"
Me.Protect Password:=strPassword
Else
Me.CommandButton1.Caption = "PROTECT SHEET"
Me.Unprotect Password:=strPassword
End If
Else
MsgBox "Invalid Password"
End If
End Sub
Thanks
If I want to assign the code to a different type of button (insert rectangle shape) and not use a CommandButton what do I change in the code please
Ray
Just right click on the shape, click on Assign Macro, and select the sub-routine that you want to be assigned to that shape.
There is no macro option to assign...I place this code in the sheet...could it be that I want the macro to work with a shape and not the commandbutton
Code:
Private Sub CommandButton1_Click()
Dim strPassword As String
Const strActualPassword As String = "ABCD"
strPassword = InputBox("Please enter the password", "Protect/Unprotect Sheet")
If strActualPassword = strPassword Then
If Me.CommandButton1.Caption = "PROTECT SHEET" Then
Me.CommandButton1.Caption = "UNPROTECT SHEET"
Me.Protect Password:=strPassword
Else
Me.CommandButton1.Caption = "PROTECT SHEET"
Me.Unprotect Password:=strPassword
End If
Else
MsgBox "Invalid Password"
End If
End Sub