Unprotecting worksheets only if you have the password
 

Hi,

I have a macro button that unprotects each worksheet in my workbook. But I
want the user only to have the ability to unprotect each worksheet after a
code is entered. The problem is that if the user chooses "Cancel" or the "X"
button to close the dialogue box, the worksheets proceed to unprotect. I've
taken care of the event that a user enters in an incorrect amount. How do I
prevent the user to unprotect each worksheet if they don't have the correct
password?

Here is my code:

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
On Error GoTo Finish
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="1111"
Next ws
Finish:
Application.ScreenUpdating = True
End Sub

Thanks in advance,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Unprotecting worksheets only if you have the password
 

Hi Mike,

How can I have VBA just provide one dialoque box asking the user to provide
a password without Excel cycling through each worksheet to unprotect by
calling up the same dialogue box....

Thanks,
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557


"Mike H" wrote:

Hi,

When you say 'Code' I assume you mean the sheet protection password. If so
modify your code thus

Private Sub CommandButton2_Click()
On Error GoTo Finish
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect
Next ws
Finish:
End Sub


Mike

"klysell" wrote:

Hi,

I have a macro button that unprotects each worksheet in my workbook. But I
want the user only to have the ability to unprotect each worksheet after a
code is entered. The problem is that if the user chooses "Cancel" or the "X"
button to close the dialogue box, the worksheets proceed to unprotect. I've
taken care of the event that a user enters in an incorrect amount. How do I
prevent the user to unprotect each worksheet if they don't have the correct
password?

Here is my code:

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
On Error GoTo Finish
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="1111"
Next ws
Finish:
Application.ScreenUpdating = True
End Sub

Thanks in advance,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Unprotecting worksheets only if you have the password
 

Provide an InputBox to the user and compare that string to the actual pwd
string. If it matches then use it to unprotect the sheets, if no match then
show a MessageBox that the pwd was wrong and Exit Sub.

Mike F
"klysell" wrote in message
...
Hi Mike,

How can I have VBA just provide one dialoque box asking the user to
provide
a password without Excel cycling through each worksheet to unprotect by
calling up the same dialogue box....

Thanks,
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557


"Mike H" wrote:

Hi,

When you say 'Code' I assume you mean the sheet protection password. If
so
modify your code thus

Private Sub CommandButton2_Click()
On Error GoTo Finish
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect
Next ws
Finish:
End Sub


Mike

"klysell" wrote:

Hi,

I have a macro button that unprotects each worksheet in my workbook.
But I
want the user only to have the ability to unprotect each worksheet
after a
code is entered. The problem is that if the user chooses "Cancel" or
the "X"
button to close the dialogue box, the worksheets proceed to unprotect.
I've
taken care of the event that a user enters in an incorrect amount. How
do I
prevent the user to unprotect each worksheet if they don't have the
correct
password?

Here is my code:

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
On Error GoTo Finish
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="1111"
Next ws
Finish:
Application.ScreenUpdating = True
End Sub

Thanks in advance,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557