Protecting Worksheets Problem

[Guest]

To explain i have the ProtectSheet Sub as a macro to lock the worksheet,
enable only selection of the unlocked cells, and locking the workbooks so
they cant unhide any other worksheets. UnprotectSheet unlocks all this.
ProtectSheetCells iand Unprotect Cell is mainly for check boxes that i have
controlling certain cells, it allows the the cell to become unlocked and then
locked again after the procedure is done.

My problem is within the ProtectSheetCells. With my code down below. I am
getting a "Run time error '5': Invalid procedure Call or Agrument", then it
highlights theThe Activeworkbook.protect line. So what am i doing wrong??

Thanks in Advance for the help!!!

Andy

--------------START OF CODE--------------------------
Option Explicit

'Ctrl+L Locks Sheet and Workbook with inputed password
Public Static Sub ProtectSheet()
Static StrPassword1 As String
Static StrPassword2 As String
Dim StrPass1 As Range

StrPassword1 = InputBox("Type a password")
StrPassword2 = InputBox("Re-type Password")
Set StrPass1 = Worksheets("BID RECAP SUMMARY").Range("A160")

If StrPassword1 <> StrPassword2 Then 'VERIFIES PASSWORD
MsgBox ("Passwords didnt match, please try again.")
Else
StrPass1 = StrPassword1
ActiveSheet.Protect Password:=StrPassword1, DrawingObjects:=True, _
Contents:=True, Scenarios:=True 'LOCK SHEET FROM CHANGES
ActiveWorkbook.Protect Password:=StrPassword1, Structure:=True 'LOCKS
WORKBOOK
ActiveSheet.EnableSelection = xlUnlockedCells 'LOCKS CELLS THAT CANT BE
SELECTED
MsgBox ("Password is set as " & StrPassword1)
End If

End Sub

'Ctrl+Shift+L UnLocks Sheet and Workbook with inputed password\
Public Static Sub UnProtectSheet()
Dim StrPassword As String
Dim StrPassword1 As Range

'STORE THE PASSWORD AT THE LOCATION BELOW TO BE USED WITH OTHER CODE
Set StrPassword1 = Worksheets("BID RECAP SUMMARY").Range("A160")

'INPUT PASSWORD TO UNLOCK SHEET AND WORKBOOK
StrPassword = InputBox("Type password to remove protection." &
vbCrLf & " Password is " & StrPassword1)
If LCase(StrPassword) <> LCase(StrPassword1) Then Exit Sub
ActiveSheet.Unprotect Password:=StrPassword
ActiveWorkbook.Unprotect Password:=StrPassword

End Sub

'PROTECTS SHEET AND WORKBOOK USED FOR CHECKBOXES
Public Sub ProtectSheetCell()
Dim StrPass11 As Range

'SETS LOCATION OF PASSWORD
Set StrPass11 = Worksheets("BID RECAP SUMMARY").Range("A160")

'LOCKS SHEET AND WORKBOOK BASED ON PASSWORD IN SET LOCATION ABOVE
ActiveSheet.Protect Password:=StrPass11, DrawingObjects:=True, _
Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Password:=StrPass11, Structure:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

'UNPROTECTS SHEET AND WORKBOOK USED FOR CHECKBOXES
Public Sub UnProtectSheetCell()
Dim StrPass As Range

'SETS LOCATION OF PASSWORD
Set StrPass = Worksheets("BID RECAP SUMMARY").Range("A160")

'UNLOCKS SHEET AND WORKBOOK BASED ON PASSWORD IN SET LOCATION ABOVE
ActiveSheet.Unprotect Password:=StrPass
ActiveWorkbook.Unprotect Password:=StrPass

End Sub
--------------END OF CODE--------------------------

 

[Guest]

Hi Andy,

The problem may be that a different password was used to protect the
workbook. The thing is, your code is very confusing at first. There's way too
many variable names being used for the same values. This may be a
contributing factor to password problems.

Another thing is it's not necessary to remove workbook protection to do
worksheet related actions. Just unprotect the sheet, do your thing, then
re-apply protection.

How I "might" handle what you're trying to do is:
1. I would declare the common variables with global scope so their values
persist while the workbook is open.
2. I use separate procedures for sheet protect/unprotect so I can access
them randomly, on demand. Ordinarily I would hard code the password in the
procedures, but since you're sharing it globally AND storing it on the
worksheet, it can be stored in a global variable and shared while the project
is open.

I wrote some sample code that you may find helpful. I changed the names of
your procedures to better reflect their nature. If you use these names,
you'll need to re-assign the shortcuts.

I hope this is helpful!
Regards,
GS

Here's the code: (It goes in a standard module)

Option Explicit

'Declare global variables
Dim gszPassword1 As String
Dim gszPassword2 As String
Dim grngPass1 As Range


Sub SetProtection() 'alias: ProtectSheet()
' Locks Sheet and Workbook with inputed password
' KeyboardShortcut: Ctrl+L

'Get password
gszPassword1 = InputBox("Type a password")
gszPassword2 = InputBox("Re-type Password")

'Reference where the password is stored
Set grngPass1 = Worksheets("BID RECAP SUMMARY").Range("A160")

If gszPassword1 <> gszPassword2 Then 'Verify password
MsgBox ("Passwords didnt match, please try again.")
Else
'Store the password
grngPass1.Value = gszPassword1

'Apply protection
WksProtect
ActiveWorkbook.Protect Password:=gszPassword1, Structure:=True

'Display notification
MsgBox ("Password is set as " & gszPassword1)
End If

End Sub

Sub RemoveProtection() 'Alias: UnProtectSheet()
' UnLocks Sheet and Workbook
' KeyboardShortcut: Ctrl+Shift+L

'Reference where the password is stored
Set grngPass1 = Worksheets("BID RECAP SUMMARY").Range("A160")

'Get the password
gszPassword1 = InputBox("Type password to remove protection." _
& vbCrLf & " Password is " & grngPass1.Value)
'Verify password
If LCase$(gszPassword1) <> LCase$(grngPass1.Value) Then Exit Sub

'Remove protection
WksUnprotect
ActiveWorkbook.Unprotect gszPassword1

End Sub

Sub WksProtect()
With ActiveSheet
.Protect Password:=gszPassword1, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
Userinterfaceonly:=True
.EnableSelection = xlUnlockedCells
End With
End Sub

Sub WksUnprotect()
ActiveSheet.Unprotect gszPassword1
End Sub