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

I used the following code, and it seems to work fine:

Public Sub UnprotectAllWorksheets()
Const strMsgBoxTitle As String = "Unprotect All Worksheets"
Dim ws As Worksheet
Dim strPassword As String

strPassword = InputBox(Prompt:="Enter the password:", _
Title:=strMsgBoxTitle)

If strPassword = "1111" _
Then
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:=strPassword
Next ws
MsgBox "All worksheets unprotected.", _
vbInformation + vbOKOnly, _
strMsgBoxTitle
Else
MsgBox "Password failed.", _
vbCritical + vbOKOnly, _
strMsgBoxTitle
End If
End Sub

I placed a simple button from the Forms toolbar on the first worksheet,
then called the macro straight from there. I didn't use a UserForm, to
eliminate extra work.

If you insist on using a UserForm (designed in the VBA editor), then you
may have to tell us more details about how this form is displayed, what
other buttons are there, which one is the default, etc. Your
"CommandButton2_Click" routine must be the default button or something.
I think a UserForm is more complicated that what you actually need.
--
Regards,
Bill Renaud

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


"Bill Renaud" wrote:

I used the following code, and it seems to work fine:

Public Sub UnprotectAllWorksheets()
Const strMsgBoxTitle As String = "Unprotect All Worksheets"
Dim ws As Worksheet
Dim strPassword As String

strPassword = InputBox(Prompt:="Enter the password:", _
Title:=strMsgBoxTitle)

If strPassword = "1111" _
Then
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:=strPassword
Next ws
MsgBox "All worksheets unprotected.", _
vbInformation + vbOKOnly, _
strMsgBoxTitle
Else
MsgBox "Password failed.", _
vbCritical + vbOKOnly, _
strMsgBoxTitle
End If
End Sub

I placed a simple button from the Forms toolbar on the first worksheet,
then called the macro straight from there. I didn't use a UserForm, to
eliminate extra work.

If you insist on using a UserForm (designed in the VBA editor), then you
may have to tell us more details about how this form is displayed, what
other buttons are there, which one is the default, etc. Your
"CommandButton2_Click" routine must be the default button or something.
I think a UserForm is more complicated that what you actually need.
--
Regards,
Bill Renaud