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

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

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

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

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

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

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

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

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

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

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

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