Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting Worksheets Problem
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, Structu=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, Structu=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-------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting Worksheets Problem
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, Structu=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protecting worksheets | Excel Worksheet Functions | |||
Protecting Worksheets | Excel Discussion (Misc queries) | |||
Protecting Worksheets | Excel Discussion (Misc queries) | |||
Protecting worksheets | Excel Worksheet Functions | |||
Protecting Worksheets | Excel Worksheet Functions |