| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting sheet did not prompt for password
I have automated sheets to be protected by a global variable g_mStrPW.
However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unprotecting sheet did not prompt for password
How do know they were protected in the first place ?
WS.Protect g_mStrPW Note no brackets. I do not use a version of Excel that supports .Protection etc so I cannot tell if that is correct. But basically you code is OK. NickHK "mhng" wrote in message ... I have automated sheets to be protected by a global variable g_mStrPW. However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unprotecting sheet did not prompt for password
I know because all my locked region work. They won't allow any editing at all.
"NickHK" wrote: How do know they were protected in the first place ? WS.Protect g_mStrPW Note no brackets. I do not use a version of Excel that supports .Protection etc so I cannot tell if that is correct. But basically you code is OK. NickHK "mhng" wrote in message ... I have automated sheets to be protected by a global variable g_mStrPW. However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unprotecting sheet did not prompt for password
Show your Uprotect code.
NickHK "mhng" wrote in message ... I know because all my locked region work. They won't allow any editing at all. "NickHK" wrote: How do know they were protected in the first place ? WS.Protect g_mStrPW Note no brackets. I do not use a version of Excel that supports .Protection etc so I cannot tell if that is correct. But basically you code is OK. NickHK "mhng" wrote in message ... I have automated sheets to be protected by a global variable g_mStrPW. However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unprotecting sheet did not prompt for password
Private Sub UnLockEm_Click()
Dim i As Long Dim PW_unlock As String Dim WS As Worksheet PW_unlock = InputBox("Password:") On Error GoTo MyErr If PW_unlock < g_mStrPW Then MsgBox "Error: Failed to unprotect worksheets! " Exit Sub Else For Each WS In ActiveWorkbook.Worksheets WS.Unprotect (PW_unlock) Next MsgBox i & " errors while unprotecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End If End Sub "NickHK" wrote: Show your Uprotect code. NickHK "mhng" wrote in message ... I know because all my locked region work. They won't allow any editing at all. "NickHK" wrote: How do know they were protected in the first place ? WS.Protect g_mStrPW Note no brackets. I do not use a version of Excel that supports .Protection etc so I cannot tell if that is correct. But basically you code is OK. NickHK "mhng" wrote in message ... I have automated sheets to be protected by a global variable g_mStrPW. However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unprotecting sheet did not prompt for password
mhng wrote: Private Sub UnLockEm_Click() Dim i As Long Dim PW_unlock As String Dim WS As Worksheet PW_unlock = InputBox("Password:") On Error GoTo MyErr If PW_unlock < g_mStrPW Then MsgBox "Error: Failed to unprotect worksheets! " Exit Sub Else For Each WS In ActiveWorkbook.Worksheets WS.Unprotect (PW_unlock) Next MsgBox i & " errors while unprotecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End If End Sub "NickHK" wrote: Show your Uprotect code. NickHK "mhng" wrote in message ... I know because all my locked region work. They won't allow any editing at all. "NickHK" wrote: How do know they were protected in the first place ? WS.Protect g_mStrPW Note no brackets. I do not use a version of Excel that supports .Protection etc so I cannot tell if that is correct. But basically you code is OK. NickHK "mhng" wrote in message ... I have automated sheets to be protected by a global variable g_mStrPW. However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub Try this: I did and it worked for me replace For Each WS In ActiveWorkbook.Worksheets WS.Unprotect (PW_unlock) with For Each WS In ActiveWorkbook.Worksheets WS.Unprotect |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unprotecting sheet did not prompt for password
From your original post: "the sheet gets unprotected without prompting for
password". That's hardly surprising as you are including the password in your unprotect code. Remove the password and add error handling to deal with the situations when the PW is wrong. NickHK "mhng" wrote in message ... Private Sub UnLockEm_Click() Dim i As Long Dim PW_unlock As String Dim WS As Worksheet PW_unlock = InputBox("Password:") On Error GoTo MyErr If PW_unlock < g_mStrPW Then MsgBox "Error: Failed to unprotect worksheets! " Exit Sub Else For Each WS In ActiveWorkbook.Worksheets WS.Unprotect (PW_unlock) Next MsgBox i & " errors while unprotecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End If End Sub "NickHK" wrote: Show your Uprotect code. NickHK "mhng" wrote in message ... I know because all my locked region work. They won't allow any editing at all. "NickHK" wrote: How do know they were protected in the first place ? WS.Protect g_mStrPW Note no brackets. I do not use a version of Excel that supports .Protection etc so I cannot tell if that is correct. But basically you code is OK. NickHK "mhng" wrote in message ... I have automated sheets to be protected by a global variable g_mStrPW. However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unprotecting sheet did not prompt for password
Thanks for your advice. I have removed the password but I don't think it will
fix my problem. When sheets are protected, anyone can easily go to any sheets, select TOOL\Protection\Unprotect Sheet, and the sheet will be unprotected without even prompting for password. This is where my problem sits. The code below to unlock my sheets is merely a way to allow quick unprotecting to all sheets rather than manually unprotecting sheet by sheet. I have a strong feeling that my protecting code is not working properly. If that's the case, then what is it? Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub "NickHK" wrote: From your original post: "the sheet gets unprotected without prompting for password". That's hardly surprising as you are including the password in your unprotect code. Remove the password and add error handling to deal with the situations when the PW is wrong. NickHK "mhng" wrote in message ... Private Sub UnLockEm_Click() Dim i As Long Dim PW_unlock As String Dim WS As Worksheet PW_unlock = InputBox("Password:") On Error GoTo MyErr If PW_unlock < g_mStrPW Then MsgBox "Error: Failed to unprotect worksheets! " Exit Sub Else For Each WS In ActiveWorkbook.Worksheets WS.Unprotect (PW_unlock) Next MsgBox i & " errors while unprotecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End If End Sub "NickHK" wrote: Show your Uprotect code. NickHK "mhng" wrote in message ... I know because all my locked region work. They won't allow any editing at all. "NickHK" wrote: How do know they were protected in the first place ? WS.Protect g_mStrPW Note no brackets. I do not use a version of Excel that supports .Protection etc so I cannot tell if that is correct. But basically you code is OK. NickHK "mhng" wrote in message ... I have automated sheets to be protected by a global variable g_mStrPW. However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unprotecting sheet did not prompt for password
I am confused as to what you want to achieve.
Your subject line is "Unprotecting sheet did not prompt for password" and now you know that is because you supplied the password when you called ..UnProtect. If the WS is protected with a password, either you (the progarmmer) include it in your code, or the user will be asked to enter it You can call .Protect with a password and UnProtect without, if that is what you mean. You can easily test if you Protect code work; try and format some cell after running the code. I don't see what the problem is. NickHK "mhng" wrote in message ... Thanks for your advice. I have removed the password but I don't think it will fix my problem. When sheets are protected, anyone can easily go to any sheets, select TOOL\Protection\Unprotect Sheet, and the sheet will be unprotected without even prompting for password. This is where my problem sits. The code below to unlock my sheets is merely a way to allow quick unprotecting to all sheets rather than manually unprotecting sheet by sheet. I have a strong feeling that my protecting code is not working properly. If that's the case, then what is it? Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub "NickHK" wrote: From your original post: "the sheet gets unprotected without prompting for password". That's hardly surprising as you are including the password in your unprotect code. Remove the password and add error handling to deal with the situations when the PW is wrong. NickHK "mhng" wrote in message ... Private Sub UnLockEm_Click() Dim i As Long Dim PW_unlock As String Dim WS As Worksheet PW_unlock = InputBox("Password:") On Error GoTo MyErr If PW_unlock < g_mStrPW Then MsgBox "Error: Failed to unprotect worksheets! " Exit Sub Else For Each WS In ActiveWorkbook.Worksheets WS.Unprotect (PW_unlock) Next MsgBox i & " errors while unprotecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End If End Sub "NickHK" wrote: Show your Uprotect code. NickHK "mhng" wrote in message ... I know because all my locked region work. They won't allow any editing at all. "NickHK" wrote: How do know they were protected in the first place ? WS.Protect g_mStrPW Note no brackets. I do not use a version of Excel that supports .Protection etc so I cannot tell if that is correct. But basically you code is OK. NickHK "mhng" wrote in message ... I have automated sheets to be protected by a global variable g_mStrPW. However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unprotecting sheet did not prompt for password
Sorry to have confused you.
To simplify the problem, my goal is to secure my spreadsheet to disallow users the ability to unprotect sheets. If they wanted to unprotect the sheets, they will need to enter a valid password. Current scenario, Admin protect sheets with a password. Users unprotect sheets without password. Thanks for your help. "NickHK" wrote: I am confused as to what you want to achieve. Your subject line is "Unprotecting sheet did not prompt for password" and now you know that is because you supplied the password when you called ..UnProtect. If the WS is protected with a password, either you (the progarmmer) include it in your code, or the user will be asked to enter it You can call .Protect with a password and UnProtect without, if that is what you mean. You can easily test if you Protect code work; try and format some cell after running the code. I don't see what the problem is. NickHK "mhng" wrote in message ... Thanks for your advice. I have removed the password but I don't think it will fix my problem. When sheets are protected, anyone can easily go to any sheets, select TOOL\Protection\Unprotect Sheet, and the sheet will be unprotected without even prompting for password. This is where my problem sits. The code below to unlock my sheets is merely a way to allow quick unprotecting to all sheets rather than manually unprotecting sheet by sheet. I have a strong feeling that my protecting code is not working properly. If that's the case, then what is it? Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub "NickHK" wrote: From your original post: "the sheet gets unprotected without prompting for password". That's hardly surprising as you are including the password in your unprotect code. Remove the password and add error handling to deal with the situations when the PW is wrong. NickHK "mhng" wrote in message ... Private Sub UnLockEm_Click() Dim i As Long Dim PW_unlock As String Dim WS As Worksheet PW_unlock = InputBox("Password:") On Error GoTo MyErr If PW_unlock < g_mStrPW Then MsgBox "Error: Failed to unprotect worksheets! " Exit Sub Else For Each WS In ActiveWorkbook.Worksheets WS.Unprotect (PW_unlock) Next MsgBox i & " errors while unprotecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End If End Sub "NickHK" wrote: Show your Uprotect code. NickHK "mhng" wrote in message ... I know because all my locked region work. They won't allow any editing at all. "NickHK" wrote: How do know they were protected in the first place ? WS.Protect g_mStrPW Note no brackets. I do not use a version of Excel that supports .Protection etc so I cannot tell if that is correct. But basically you code is OK. NickHK "mhng" wrote in message ... I have automated sheets to be protected by a global variable g_mStrPW. However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unprotecting sheet did not prompt for password
Hi All,
sorry interupting, in order to try make it sure and clear, I think if we protect sheet(s) with a password by VBA or not, we'll still promted by password confirmation automatically, but if we protect witout password we'll never promted a password is that r8? thanks, Halim mhng menuliskan: Sorry to have confused you. To simplify the problem, my goal is to secure my spreadsheet to disallow users the ability to unprotect sheets. If they wanted to unprotect the sheets, they will need to enter a valid password. Current scenario, Admin protect sheets with a password. Users unprotect sheets without password. Thanks for your help. "NickHK" wrote: I am confused as to what you want to achieve. Your subject line is "Unprotecting sheet did not prompt for password" and now you know that is because you supplied the password when you called ..UnProtect. If the WS is protected with a password, either you (the progarmmer) include it in your code, or the user will be asked to enter it You can call .Protect with a password and UnProtect without, if that is what you mean. You can easily test if you Protect code work; try and format some cell after running the code. I don't see what the problem is. NickHK "mhng" wrote in message ... Thanks for your advice. I have removed the password but I don't think it will fix my problem. When sheets are protected, anyone can easily go to any sheets, select TOOL\Protection\Unprotect Sheet, and the sheet will be unprotected without even prompting for password. This is where my problem sits. The code below to unlock my sheets is merely a way to allow quick unprotecting to all sheets rather than manually unprotecting sheet by sheet. I have a strong feeling that my protecting code is not working properly. If that's the case, then what is it? Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub "NickHK" wrote: From your original post: "the sheet gets unprotected without prompting for password". That's hardly surprising as you are including the password in your unprotect code. Remove the password and add error handling to deal with the situations when the PW is wrong. NickHK "mhng" wrote in message ... Private Sub UnLockEm_Click() Dim i As Long Dim PW_unlock As String Dim WS As Worksheet PW_unlock = InputBox("Password:") On Error GoTo MyErr If PW_unlock < g_mStrPW Then MsgBox "Error: Failed to unprotect worksheets! " Exit Sub Else For Each WS In ActiveWorkbook.Worksheets WS.Unprotect (PW_unlock) Next MsgBox i & " errors while unprotecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End If End Sub "NickHK" wrote: Show your Uprotect code. NickHK "mhng" wrote in message ... I know because all my locked region work. They won't allow any editing at all. "NickHK" wrote: How do know they were protected in the first place ? WS.Protect g_mStrPW Note no brackets. I do not use a version of Excel that supports .Protection etc so I cannot tell if that is correct. But basically you code is OK. NickHK "mhng" wrote in message ... I have automated sheets to be protected by a global variable g_mStrPW. However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Unprotecting sheet did not prompt for password
If the WS is password protected by the admin, the user WILL be asked for the
password to unprotect. You do know that code is available the will break the protection password in less than a second. NickHK "mhng" wrote in message ... Sorry to have confused you. To simplify the problem, my goal is to secure my spreadsheet to disallow users the ability to unprotect sheets. If they wanted to unprotect the sheets, they will need to enter a valid password. Current scenario, Admin protect sheets with a password. Users unprotect sheets without password. Thanks for your help. "NickHK" wrote: I am confused as to what you want to achieve. Your subject line is "Unprotecting sheet did not prompt for password" and now you know that is because you supplied the password when you called ..UnProtect. If the WS is protected with a password, either you (the progarmmer) include it in your code, or the user will be asked to enter it You can call .Protect with a password and UnProtect without, if that is what you mean. You can easily test if you Protect code work; try and format some cell after running the code. I don't see what the problem is. NickHK "mhng" wrote in message ... Thanks for your advice. I have removed the password but I don't think it will fix my problem. When sheets are protected, anyone can easily go to any sheets, select TOOL\Protection\Unprotect Sheet, and the sheet will be unprotected without even prompting for password. This is where my problem sits. The code below to unlock my sheets is merely a way to allow quick unprotecting to all sheets rather than manually unprotecting sheet by sheet. I have a strong feeling that my protecting code is not working properly. If that's the case, then what is it? Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub "NickHK" wrote: From your original post: "the sheet gets unprotected without prompting for password". That's hardly surprising as you are including the password in your unprotect code. Remove the password and add error handling to deal with the situations when the PW is wrong. NickHK "mhng" wrote in message ... Private Sub UnLockEm_Click() Dim i As Long Dim PW_unlock As String Dim WS As Worksheet PW_unlock = InputBox("Password:") On Error GoTo MyErr If PW_unlock < g_mStrPW Then MsgBox "Error: Failed to unprotect worksheets! " Exit Sub Else For Each WS In ActiveWorkbook.Worksheets WS.Unprotect (PW_unlock) Next MsgBox i & " errors while unprotecting", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End If End Sub "NickHK" wrote: Show your Uprotect code. NickHK "mhng" wrote in message ... I know because all my locked region work. They won't allow any editing at all. "NickHK" wrote: How do know they were protected in the first place ? WS.Protect g_mStrPW Note no brackets. I do not use a version of Excel that supports .Protection etc so I cannot tell if that is correct. But basically you code is OK. NickHK "mhng" wrote in message ... I have automated sheets to be protected by a global variable g_mStrPW. However, when trying to unprotect individual sheet, the sheet gets unprotected without prompting for password. This is bad and made protecting sheets meaningless. Here is the code used to protect all sheets. Private Sub LockEM_Click() Dim i As Long Dim WS As Worksheet g_mStrPW = InputBox("Password:") On Error GoTo MyErr For Each WS In ActiveWorkbook.Worksheets WS.Protect (g_mStrPW) If WS.Protection.AllowUsingPivotTables = False Then WS.Protect AllowUsingPivotTables:=True WS.Protect AllowFiltering:=True End If Next MsgBox i & " errors found", vbInformation Exit Sub MyErr: i = i + 1 Resume Next End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro asking for a password twice when unprotecting sheet | Excel Programming | |||
| Password Protecting/Unprotecting Via Code | Excel Programming | |||
| Unprotecting password protected workbook | Excel Discussion (Misc queries) | |||
| password prompt to unhide sheet | Excel Programming | |||
| Simple password function - unprotecting sheets | Excel Programming | |||
Linear Mode
