Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a password in VBA ActiveSheet.protect & ActiveSheet.unprotect?
I have about a dozen worksheets in a workbook that are password protected. I
want to use a macro to unprotect each sheet (one at a time), lock or unlock some cells, and then password protect the worksheet before moving to the next sheet. The password is the same for all the worksheets. I would like the macro to prompt one time for the password and then use that input to perform the unprotect and protect. I got the VBA code below when I recorded a macro. Unfortunately, using this code, the user is prompted each time the worksheet is unprotected, the cell locks are set, and then the worksheet is protected again but without the password. Sheets("Special Loans").Select ActiveSheet.Unprotect Cells.Select Range("M1").Activate Selection.Locked = True Selection.FormulaHidden = False Range("A1").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWorkbook.Save |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a password in VBA ActiveSheet.protect & ActiveSheet.unprotect?
Hi Jim,
Try something like: '=========== Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim PWORD As String PWORD = InputBox(Prompt:="Insert password") Set WB = ThisWorkbook For Each SH In WB.Worksheets With SH On Error GoTo XIT .Unprotect PWORD With .Range("M1") .Locked = True .FormulaHidden = False End With .Protect password:=PWORD, _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True End With Next SH WB.Save Exit Sub XIT: MsgBox Prompt:="Password not recognised" End Sub '<<=========== --- Regards. Norman "Jim K." <Jim wrote in message ... I have about a dozen worksheets in a workbook that are password protected. I want to use a macro to unprotect each sheet (one at a time), lock or unlock some cells, and then password protect the worksheet before moving to the next sheet. The password is the same for all the worksheets. I would like the macro to prompt one time for the password and then use that input to perform the unprotect and protect. I got the VBA code below when I recorded a macro. Unfortunately, using this code, the user is prompted each time the worksheet is unprotected, the cell locks are set, and then the worksheet is protected again but without the password. Sheets("Special Loans").Select ActiveSheet.Unprotect Cells.Select Range("M1").Activate Selection.Locked = True Selection.FormulaHidden = False Range("A1").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWorkbook.Save |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a password in VBA ActiveSheet.protect & ActiveSheet.unprot
That was just the code I was looking for. THANKS!!!
"Norman Jones" wrote: Hi Jim, Try something like: '=========== Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim PWORD As String PWORD = InputBox(Prompt:="Insert password") Set WB = ThisWorkbook For Each SH In WB.Worksheets With SH On Error GoTo XIT .Unprotect PWORD With .Range("M1") .Locked = True .FormulaHidden = False End With .Protect password:=PWORD, _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True End With Next SH WB.Save Exit Sub XIT: MsgBox Prompt:="Password not recognised" End Sub '<<=========== --- Regards. Norman "Jim K." <Jim wrote in message ... I have about a dozen worksheets in a workbook that are password protected. I want to use a macro to unprotect each sheet (one at a time), lock or unlock some cells, and then password protect the worksheet before moving to the next sheet. The password is the same for all the worksheets. I would like the macro to prompt one time for the password and then use that input to perform the unprotect and protect. I got the VBA code below when I recorded a macro. Unfortunately, using this code, the user is prompted each time the worksheet is unprotected, the cell locks are set, and then the worksheet is protected again but without the password. Sheets("Special Loans").Select ActiveSheet.Unprotect Cells.Select Range("M1").Activate Selection.Locked = True Selection.FormulaHidden = False Range("A1").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveWorkbook.Save |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying new activesheet after other activesheet is hidden? | Excel Programming | |||
Difference between Activesheet.unprotect | Excel Discussion (Misc queries) | |||
ActiveSheet.Unprotect | Excel Programming | |||
ActiveSheet.Protect | Excel Programming | |||
ActiveSheet.Protect | Excel Programming |