Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting/Reprotecting
I have a VBA code in Module1 of a workbook which protects all the sheets with
the same password. The code is Sub LockEm() Dim i As Long Dim PW As String Dim WS As Worksheet PW = InputBox("Enter password to protect all sheets:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (PW) Next MsgBox i & " errors while protecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub Now, I have a macro in the same module in which I would like to unprotect the sheet with the password entered when protecting all the sheet, execute some activities and then reprotect it with the same password, i.e. lets say I i have the macro Sub xxx() Range("C4").Select ActiveCell.FormulaR1C1 = "cc" End Sub Where and how do I enter the code? I assume I need to make PW public or something (I'm quite new at VBA) Secondly, I would like to apply the same thing as above to a worksheet event that I have in a specific sheet Any ideas? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting/Reprotecting
You could put the code in Module1 (say we call it UnLockEm) and to allow you
to work with one sheet at a time let's add a parameter for the worksheet name. Make it a Public Sub so it can be called from any other module. It appears from your post that the user will not know the password so you would need to hard code it, which does open it up to anyone with the knowledge of how to get into VBA, so that is a concern you will have to decide about: Public Sub UnLockEm(ThisSheetName as String) With Sheets(ThisSheetName) .Unprotect("Password") .Range("C4").FormulaR1C1 = "cc" .Protect("Password") End With End Sub To link it to your Worksheet event procedures, just make the call as shown in this example: Private Sub Worksheet_Change(ByVal Target as Range) .... UnLockEm(Me.Name) .... End Sub "Adam" wrote: I have a VBA code in Module1 of a workbook which protects all the sheets with the same password. The code is Sub LockEm() Dim i As Long Dim PW As String Dim WS As Worksheet PW = InputBox("Enter password to protect all sheets:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (PW) Next MsgBox i & " errors while protecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub Now, I have a macro in the same module in which I would like to unprotect the sheet with the password entered when protecting all the sheet, execute some activities and then reprotect it with the same password, i.e. lets say I i have the macro Sub xxx() Range("C4").Select ActiveCell.FormulaR1C1 = "cc" End Sub Where and how do I enter the code? I assume I need to make PW public or something (I'm quite new at VBA) Secondly, I would like to apply the same thing as above to a worksheet event that I have in a specific sheet Any ideas? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting/Reprotecting
Thanks,
But is it not possible to for xls to remember the PW and then call it from another sub. The procedure will be as follows. First i run the macro LockEm() and assign a password. Looking at the code the password is stored in the string PW Secondly I send out the file with the receiver unknowing of the password Thirdly the recipient may run a macro. Since the macro contains cells that are protected it will of course complain. Therefore I need the macro that the recipient is running to start by unprotecting the sheet with the password that I assigned (i.e. the string PW), run the macro and then protect it with same password PW. I have another macro in the same module as LockEm() called UnlockEm() which prompts the user to insert the password entered in the macro LockEm(). This I need to do also for the other macro but I do not know how to get the macro to recognize the PW parameter. I tried Worksheets("Sheet1").Unprotect(PW) in the beginning of the macro but it did not recognized the parameter /Adam "K Dales" skrev: You could put the code in Module1 (say we call it UnLockEm) and to allow you to work with one sheet at a time let's add a parameter for the worksheet name. Make it a Public Sub so it can be called from any other module. It appears from your post that the user will not know the password so you would need to hard code it, which does open it up to anyone with the knowledge of how to get into VBA, so that is a concern you will have to decide about: Public Sub UnLockEm(ThisSheetName as String) With Sheets(ThisSheetName) .Unprotect("Password") .Range("C4").FormulaR1C1 = "cc" .Protect("Password") End With End Sub To link it to your Worksheet event procedures, just make the call as shown in this example: Private Sub Worksheet_Change(ByVal Target as Range) ... UnLockEm(Me.Name) ... End Sub "Adam" wrote: I have a VBA code in Module1 of a workbook which protects all the sheets with the same password. The code is Sub LockEm() Dim i As Long Dim PW As String Dim WS As Worksheet PW = InputBox("Enter password to protect all sheets:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (PW) Next MsgBox i & " errors while protecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub Now, I have a macro in the same module in which I would like to unprotect the sheet with the password entered when protecting all the sheet, execute some activities and then reprotect it with the same password, i.e. lets say I i have the macro Sub xxx() Range("C4").Select ActiveCell.FormulaR1C1 = "cc" End Sub Where and how do I enter the code? I assume I need to make PW public or something (I'm quite new at VBA) Secondly, I would like to apply the same thing as above to a worksheet event that I have in a specific sheet Any ideas? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting/Reprotecting
You can store worksheet-specific data in "Custom Properties", that as far as
I can tell, doesn't show up anywhere, so it should be safe for passwords. public sub LockEm() dim PW as string dim objSheet as worksheet PW = InputBox("Enter password to protect all sheets:") for each objSheet in sheets objSheet.Protect PW objSheet.CustomProperties.Add "PassCode", PW next end sub public sub ChangeSheet(byval strSheet as string) dim PassCode as string with sheets(strSheet) PassCode = .CustomProperties(1).value ' can access it by index only .Unprotect PassCode ' do some changes .Protect PassCode end with end sub There is a typical problem here though with lame Microsoft documentation on this topic. I can't find where these properties are stored. Using something that you don't know much about...well...use @ your own risk. "Adam" wrote: Thanks, But is it not possible to for xls to remember the PW and then call it from another sub. The procedure will be as follows. First i run the macro LockEm() and assign a password. Looking at the code the password is stored in the string PW Secondly I send out the file with the receiver unknowing of the password Thirdly the recipient may run a macro. Since the macro contains cells that are protected it will of course complain. Therefore I need the macro that the recipient is running to start by unprotecting the sheet with the password that I assigned (i.e. the string PW), run the macro and then protect it with same password PW. I have another macro in the same module as LockEm() called UnlockEm() which prompts the user to insert the password entered in the macro LockEm(). This I need to do also for the other macro but I do not know how to get the macro to recognize the PW parameter. I tried Worksheets("Sheet1").Unprotect(PW) in the beginning of the macro but it did not recognized the parameter /Adam "K Dales" skrev: You could put the code in Module1 (say we call it UnLockEm) and to allow you to work with one sheet at a time let's add a parameter for the worksheet name. Make it a Public Sub so it can be called from any other module. It appears from your post that the user will not know the password so you would need to hard code it, which does open it up to anyone with the knowledge of how to get into VBA, so that is a concern you will have to decide about: Public Sub UnLockEm(ThisSheetName as String) With Sheets(ThisSheetName) .Unprotect("Password") .Range("C4").FormulaR1C1 = "cc" .Protect("Password") End With End Sub To link it to your Worksheet event procedures, just make the call as shown in this example: Private Sub Worksheet_Change(ByVal Target as Range) ... UnLockEm(Me.Name) ... End Sub "Adam" wrote: I have a VBA code in Module1 of a workbook which protects all the sheets with the same password. The code is Sub LockEm() Dim i As Long Dim PW As String Dim WS As Worksheet PW = InputBox("Enter password to protect all sheets:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (PW) Next MsgBox i & " errors while protecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub Now, I have a macro in the same module in which I would like to unprotect the sheet with the password entered when protecting all the sheet, execute some activities and then reprotect it with the same password, i.e. lets say I i have the macro Sub xxx() Range("C4").Select ActiveCell.FormulaR1C1 = "cc" End Sub Where and how do I enter the code? I assume I need to make PW public or something (I'm quite new at VBA) Secondly, I would like to apply the same thing as above to a worksheet event that I have in a specific sheet Any ideas? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unprotecting a worksheet | Excel Worksheet Functions | |||
Unprotecting a VBA Project | Excel Programming | |||
Unprotecting! | Excel Programming | |||
Unprotecting! | Excel Programming | |||
Unprotecting! | Excel Programming |