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