Prompt for password

[Tanya]

Hi Dave
The code I have now closes the form once commandbutton1 is selected
regardless of whether the password was correct or not. How do I change the
code to prompt user to enter the password again?

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim eCtr As Long
Dim wbUnprotected As Boolean
Dim myStr As String
Dim myPwd As String

Application.ScreenUpdating = False

myPwd = Me.txtUnprotect.Value

eCtr = 0
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True _
Or ws.ProtectDrawingObjects = True _
Or ws.ProtectScenarios = True Then
On Error Resume Next
ws.Unprotect Password:=myPwd
If Err.Number <> 0 Then
eCtr = eCtr + 1
Err.Clear
End If
On Error GoTo 0
End If
Next ws

wbUnprotected = True

If ActiveWorkbook.ProtectStructure = True _
Or ActiveWorkbook.ProtectWindows = True Then
On Error Resume Next
ActiveWorkbook.Unprotect Password:=myPwd
If Err.Number <> 0 Then
wbUnprotected = False
Err.Clear
End If
On Error GoTo 0
End If

myStr = ""
If wbUnprotected = False Then
myStr = "Workbook Not Unprotected"
End If

If eCtr > 0 Then
If myStr <> "" Then
myStr = myStr & vbLf
End If
myStr = myStr & eCtr & " worksheets not unprotected!"
End If

If myStr = "" Then
myStr = "Workbook and all worksheets unprotected"
End If

Me.Label1.Caption = myStr
Application.ScreenUpdating = True
Me.Label1.Caption = ""
Unload Me

If wbUnprotected = True Then

Worksheets("Admin").Select
Range("A1").Select
ActiveWindow.DisplayWorkbookTabs = True

End If

End Sub

Kind Regards
Tanya

 

[Dave Peterson]

'remove this
'If myStr = "" Then
' myStr = "Workbook and all worksheets unprotected"
'End If

Me.Label1.Caption = myStr
Application.ScreenUpdating = True
'I would display that message in the label if something was wrong.
Me.Label1.Caption = myStr
if mystr = "" then
'everything was ok
Unload Me
end if

====
But I would display that message in the label if something was wrong.

 

[Dave Peterson]

You were changing the label's caption twice in that second portion of your code.

I changed the second line and I should have just deleted it:

'remove this
'If myStr = "" Then
' myStr = "Workbook and all worksheets unprotected"
'End If

Me.Label1.Caption = myStr
Application.ScreenUpdating = True
if mystr = "" then
'everything was ok
Unload Me
end if

 

[Tanya]

Thank you again..

I made the following changes

If wbUnprotected = False Then <<<<<<<<<<<<<<<

Me.Label1.Caption = myStr
Application.ScreenUpdating = True
Me.Label1.Caption = "Wrong Password" <<<<<<<<<<<<<<

End If

If wbUnprotected = True Then
Unload Me <<<<<<<<<<<<<<<<

I really appreciate your support, through this exercise I have learnt a lot.

Kind Regards
Tanya

 

[Dave Peterson]

Won't that hide the userform (and the warning about worksheets not being
unprotected) if any of the worksheets unprotection fails?

 

[Tanya]

Hi Dave,
I am not having any problems, when I type in the incorrect password the
label displays "Administrator Only" and if I type the correct password the
workbook is unlocked and I go straight to the ADMIN sheet, with tabs in view.

My only question is 'what is the following code doing exactly?' Because at
no time do I see the words "workbook not unprotected" OR "worksheets not
unprotected".

myStr = ""
If wbUnprotected = False Then
myStr = "Workbook Not Unprotected"
End If

If eCtr > 0 Then
If myStr <> "" Then
myStr = myStr & vbLf
End If
myStr = myStr & eCtr & " worksheets not unprotected!"
End If

 

[Dave Peterson]

First, myStr is a variable that's serving two purposes. It's used to display
some text to the user--and it's also used as an indicator that something went
wrong. (If mystr = "", then everything was ok.)

myStr = ""
If wbUnprotected = False Then
myStr = "Workbook Not Unprotected"
End If

If eCtr > 0 Then
If myStr <> "" Then
myStr = myStr & vbLf
End If
myStr = myStr & eCtr & " worksheets not unprotected!"
End If

If the workbook wasn't unprotected, then mystr is set to that "workbook not
unprotected" message.

Then it checks to see if ectr > 0. ectr is the number of sheets that are still
protected.

If that value is 0, then it doesn't change myStr.

But if that value is not zero, it checks to see what's in mystr.
If you have that workbook warning message, it adds a vblf (newline) to the
message.

Then it appends the worksheet warning message to the string.

So you'll end up with mystr that looks like:

1. (empty)

2. Workbook Not Unprotected

3. ## worksheets not unprotected

4. Workbook Not Unprotected
## worksheets not unprotected

(on two lines in the label.)

 

[Tanya]

Hi Dave
Thank you for explaing this code to me.

This explains why I have never seen the text strings you have coded i.e.
each time the code has been run there has been no unprotected sheets.
Therefore I guess you have put the code in incase something went wrong, and
since I commented out this section of the code it hasn't make any difference.

My next question if you wouldn't mind, is instead of having to use the tab
key to select the 'commandbutton' with 'OK', how can you make this the active
commandbutton? because invariably a user will press enter on the keyboard and
not use the tab key which closes the form.

Kind Regards
Tanya

 

[Dave Peterson]

This portion of the code:

With Me.CommandButton1
.Caption = "Cancel"
.Cancel = True
.Enabled = True
End With

With Me.CommandButton2
.Caption = "Ok"
.Default = True
.Enabled = False
End With

Made commandbutton1 (the cancel button) get "clicked" when the user hit the
escape key. And it made commandbutton2 (the Ok button) get clicked when the
user hit enter.

That's what .cancel and .default do for the buttons.

I would guess that since you changed the code, you didn't include this stuff in
your version.

Hi Dave
Thank you for explaing this code to me.

This explains why I have never seen the text strings you have coded i.e.
each time the code has been run there has been no unprotected sheets.
Therefore I guess you have put the code in incase something went wrong, and
since I commented out this section of the code it hasn't make any difference.

My next question if you wouldn't mind, is instead of having to use the tab
key to select the 'commandbutton' with 'OK', how can you make this the active
commandbutton? because invariably a user will press enter on the keyboard and
not use the tab key which closes the form.

Kind Regards
Tanya

 

[Tanya]

Hi Dave
That makes sence and works as you said it would, thank you.
However, there is no error message now when the password is incorrect. Below
is the code at present - you will notice that I have commented out the label
"":


Option Explicit

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim eCtr As Long
Dim wbUnprotected As Boolean
Dim myStr As String
Dim myPwd As String

Application.ScreenUpdating = False

myPwd = Me.txtUnprotect.Value

eCtr = 0
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True _
Or ws.ProtectDrawingObjects = True _
Or ws.ProtectScenarios = True Then
On Error Resume Next
ws.Unprotect Password:=myPwd
If Err.Number <> 0 Then
eCtr = eCtr + 1
Err.Clear
End If
On Error GoTo 0
End If
Next ws

wbUnprotected = True

If ActiveWorkbook.ProtectStructure = True _
Or ActiveWorkbook.ProtectWindows = True Then
On Error Resume Next
ActiveWorkbook.Unprotect Password:=myPwd
If Err.Number <> 0 Then
wbUnprotected = False
Err.Clear
End If
On Error GoTo 0
End If

myStr = ""
If wbUnprotected = False Then
myStr = "Workbook Not Unprotected"
End If

If eCtr > 0 Then
If myStr <> "" Then
myStr = myStr & vbLf
End If
myStr = myStr & eCtr & " worksheets not unprotected!"
End If


'If wbUnprotected = False Then

'Me.Label1.Caption = myStr
'Application.ScreenUpdating = True
'Me.Label1.Caption = "Administrator Only"
'txtUnprotect = ""

'End If

If wbUnprotected = True Then
Unload Me

Worksheets("Admin").Select
Range("A1").Select
ActiveWindow.DisplayWorkbookTabs = True

End If

End Sub

Private Sub txtUnprotect_Change()
Me.CommandButton2.Enabled = CBool(Len(Me.txtUnprotect.Value) > 0)
End Sub

Private Sub UserForm_Initialize()

Me.Caption = "Enter Administrator Password"


With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
.Enabled = False
End With

With Me.CommandButton1
.Caption = "OK"
.Default = True
.Enabled = True
End With

With Me.txtUnprotect
.SetFocus
.PasswordChar = "*"
End With

Me.Label1.Caption = ""
End Sub


Private Sub CommandButton2_Click()
Unload Me
End Sub


Kind Regards
Tanya

 

[Dave Peterson]

You check to see if the workbook is unprotected. I was checking to see if there
were any errors by looking at mystr (mystr="" meant no errors).

Maybe something like this...

If mystr <> "" then
Me.Label1.Caption = myStr
else
Unload Me
end if