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-------------------------- |
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 |