Prompt for password
 

Hi I have the following code and want to prompt the user for the correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

Prompt for password
 

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson

Prompt for password
 

Hi Dave
Thank you for your quick response to my problem.

I ran the code as you have it and got a 'compile error' on the line -----
--------dim myPwd as Password

My second question is "do I replace 'myPwd' with the password I want to use?
or do I need another line?

cheers
Tanya

"Dave Peterson" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson

Prompt for password
 

tanya:

dave just had a typo.

try this

Dim myPwd As String

--


Gary


"Tanya" wrote in message
...
Hi Dave
Thank you for your quick response to my problem.

I ran the code as you have it and got a 'compile error' on the line -----
--------dim myPwd as Password

My second question is "do I replace 'myPwd' with the password I want to use?
or do I need another line?

cheers
Tanya

"Dave Peterson" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson

Prompt for password
 

Thank you Gary. I am glad you cleared that up.

Is there any way to all the user to try again if they get the password
wrong? because it comes up with a run-time error if the user puts the wrong
password in..

Kind Regards
Tanya

"Gary Keramidas" wrote:

tanya:

dave just had a typo.

try this

Dim myPwd As String

--


Gary


"Tanya" wrote in message
...
Hi Dave
Thank you for your quick response to my problem.

I ran the code as you have it and got a 'compile error' on the line -----
--------dim myPwd as Password

My second question is "do I replace 'myPwd' with the password I want to use?
or do I need another line?

cheers
Tanya

"Dave Peterson" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson

Prompt for password
 

Thank you Dave

By the way, when I took out application.screenupdating lines the macro
appeared to take longer and went through each sheet one by one [there are
over 100 sheets in this workbook].

Kind Regards
Tanya Duffy

"Dave Peterson" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson

Prompt for password
 

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Dim myPwd As String
Application.ScreenUpdating = False
Set ws = Worksheets(1)
On Error Resume Next
Do
myPwd = GetPassword
ws.Unprotect Password:=myPwd
If myPwd < "" Then

If ws.ProtectContents Then

MsgBox "Invalid password, try again", vbOKOnly +
vbInformation, "Password input"
End If
End If
Loop Until Not ws.ProtectContents Or myPwd = ""
On Error GoTo 0
If myPwd < "" Then

For Each ws In ActiveWorkbook.Worksheets

If ws.ProtectContents = True Then

ws.Unprotect Password:=myPwd
End If
Next ws
ActiveWorkbook.Unprotect Password:=myPwd
Application.ScreenUpdating = True
End If
End Sub

Private Function GetPassword() As Variant
GetPassword = InputBox(Prompt:="Please enter the common password")
End Function

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tanya" wrote in message
...
Thank you Gary. I am glad you cleared that up.

Is there any way to all the user to try again if they get the password
wrong? because it comes up with a run-time error if the user puts the
wrong
password in..

Kind Regards
Tanya

"Gary Keramidas" wrote:

tanya:

dave just had a typo.

try this

Dim myPwd As String

--


Gary


"Tanya" wrote in message
...
Hi Dave
Thank you for your quick response to my problem.

I ran the code as you have it and got a 'compile error' on the
line -----
--------dim myPwd as Password

My second question is "do I replace 'myPwd' with the password I want to
use?
or do I need another line?

cheers
Tanya

"Dave Peterson" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the
correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson

Prompt for password
 

Thank you Bob. Worked a treat.

Much appreciated.

Kind Regards
Tanya


"Bob Phillips" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Dim myPwd As String
Application.ScreenUpdating = False
Set ws = Worksheets(1)
On Error Resume Next
Do
myPwd = GetPassword
ws.Unprotect Password:=myPwd
If myPwd < "" Then

If ws.ProtectContents Then

MsgBox "Invalid password, try again", vbOKOnly +
vbInformation, "Password input"
End If
End If
Loop Until Not ws.ProtectContents Or myPwd = ""
On Error GoTo 0
If myPwd < "" Then

For Each ws In ActiveWorkbook.Worksheets

If ws.ProtectContents = True Then

ws.Unprotect Password:=myPwd
End If
Next ws
ActiveWorkbook.Unprotect Password:=myPwd
Application.ScreenUpdating = True
End If
End Sub

Private Function GetPassword() As Variant
GetPassword = InputBox(Prompt:="Please enter the common password")
End Function

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tanya" wrote in message
...
Thank you Gary. I am glad you cleared that up.

Is there any way to all the user to try again if they get the password
wrong? because it comes up with a run-time error if the user puts the
wrong
password in..

Kind Regards
Tanya

"Gary Keramidas" wrote:

tanya:

dave just had a typo.

try this

Dim myPwd As String

--


Gary


"Tanya" wrote in message
...
Hi Dave
Thank you for your quick response to my problem.

I ran the code as you have it and got a 'compile error' on the
line -----
--------dim myPwd as Password

My second question is "do I replace 'myPwd' with the password I want to
use?
or do I need another line?

cheers
Tanya

"Dave Peterson" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the
correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson

Prompt for password
 

Thanks for the correction, Gary.

(My brain and fingers had a disconnect!)

Gary Keramidas wrote:

tanya:

dave just had a typo.

try this

Dim myPwd As String

--

Gary

"Tanya" wrote in message
...
Hi Dave
Thank you for your quick response to my problem.

I ran the code as you have it and got a 'compile error' on the line -----
--------dim myPwd as Password

My second question is "do I replace 'myPwd' with the password I want to use?
or do I need another line?

cheers
Tanya

"Dave Peterson" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson


--

Dave Peterson

Prompt for password
 

You're right.

I see the flickering in the worksheet tabs when I unprotect the sheets.
Curiously, I don't see it when I protect them.

Tanya wrote:

Thank you Dave

By the way, when I took out application.screenupdating lines the macro
appeared to take longer and went through each sheet one by one [there are
over 100 sheets in this workbook].

Kind Regards
Tanya Duffy

"Dave Peterson" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson


--

Dave Peterson

Prompt for password
 

Hi Dave

I have noticed that when the password is typed you can see the characters.
How can I get the characters to display as *******.

Kindest Regards
Tanya

"Dave Peterson" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson

Prompt for password
 

You can't mask the characters in an InputBox. You can create a UserForm with
a TextBox control and set the PasswordChar property of the TextBox to the
character that should be displayed in the TextBox when the user types in the
password.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"Tanya" wrote in message
...
Hi Dave

I have noticed that when the password is typed you can see the characters.
How can I get the characters to display as *******.

Kindest Regards
Tanya

"Dave Peterson" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the
correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson

Prompt for password
 

Hi Chip
I am not very experienced in using forms or vba, and have created a basic
form called frmPassword and changed the PasswordChar property of the textbox
to the '*' character.
My question now is how do I set up the code for this? I have created a
commandbutton called CmdOK, below is the code I am using: Now I am presuming
I need to change the following code line

myPwd = Application.InputBox("Please enter correct password")
TO
myPwd = frmPassword

THEN
Does the code below become the code for the CmdOK and my original command
button needs a new code which will prompt frmPassword?

Function UnProtect_Workbook() As Boolean
'Unprotect workbook
Dim ws As Worksheet
Dim myPwd As String
Application.ScreenUpdating = False
Set ws = Worksheets(1)
On Error Resume Next
Do
myPwd = Application.InputBox("Please enter correct password")
If myPwd Then 'OK clicked
ws.Unprotect Password:=myPwd
If ws.ProtectContents Then
MsgBox "Invalid password, try again", vbOKOnly +
vbInformation, "Password input"
End If
Else 'Cancel clicked
Exit Function 'UnProtect_Workbook still set to False as this is
Default
End If
Loop Until Not ws.ProtectContents
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect Password:=myPwd
End If
Next ws
ActiveWorkbook.Unprotect Password:=myPwd
Application.ScreenUpdating = True
UnProtect_Workbook = True
End Function


Kind Regards
Tanya

Prompt for password
 

I'm not Chip, but I created a small userform with 2 buttons, a textbox and a
label and used this code behind it:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_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.TextBox1.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
On Error Resume Next
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

End Sub
Private Sub TextBox1_Change()
Me.CommandButton2.Enabled = CBool(Len(Me.TextBox1.Value) 0)
End Sub
Private Sub UserForm_Initialize()

Me.Caption = "Enter Password"

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

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

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

Me.Label1.Caption = ""
End Sub

=======
I also put this code in a general workbook that would show the userform:

Option Explicit
Sub UnprotectLots()
UserForm1.Show
End Sub



Tanya wrote:

Hi Chip
I am not very experienced in using forms or vba, and have created a basic
form called frmPassword and changed the PasswordChar property of the textbox
to the '*' character.
My question now is how do I set up the code for this? I have created a
commandbutton called CmdOK, below is the code I am using: Now I am presuming
I need to change the following code line

myPwd = Application.InputBox("Please enter correct password")
TO
myPwd = frmPassword

THEN
Does the code below become the code for the CmdOK and my original command
button needs a new code which will prompt frmPassword?

Function UnProtect_Workbook() As Boolean
'Unprotect workbook
Dim ws As Worksheet
Dim myPwd As String
Application.ScreenUpdating = False
Set ws = Worksheets(1)
On Error Resume Next
Do
myPwd = Application.InputBox("Please enter correct password")
If myPwd Then 'OK clicked
ws.Unprotect Password:=myPwd
If ws.ProtectContents Then
MsgBox "Invalid password, try again", vbOKOnly +
vbInformation, "Password input"
End If
Else 'Cancel clicked
Exit Function 'UnProtect_Workbook still set to False as this is
Default
End If
Loop Until Not ws.ProtectContents
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect Password:=myPwd
End If
Next ws
ActiveWorkbook.Unprotect Password:=myPwd
Application.ScreenUpdating = True
UnProtect_Workbook = True
End Function

Kind Regards
Tanya

--

Dave Peterson

Prompt for password
 

Thank you for this Dave

I have followed your instructions, changing names where necessary i.e.
CommandButton1 and CommandButton2 around the other way, since I had already
created them.

The only problem I am having is an error Compile error Method or data member
not found

The line it refers to is Me.Label1.Caption = myStr

I tried chaning Me.Label1.Caption to Me.txtUnprotect.Caption because I had
renamed the text box to txtunprotect

Obviously I am still doing something wrong. Could you please advise.

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
On Error Resume Next
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.txtUnprotect.Caption = myStr
Application.ScreenUpdating = True
Me.txtUnprotect.Caption = ""

End Sub


Kind Regards
Tanya



"Dave Peterson" wrote:

I'm not Chip, but I created a small userform with 2 buttons, a textbox and a
label and used this code behind it:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_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.TextBox1.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
On Error Resume Next
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

End Sub
Private Sub TextBox1_Change()
Me.CommandButton2.Enabled = CBool(Len(Me.TextBox1.Value) 0)
End Sub
Private Sub UserForm_Initialize()

Me.Caption = "Enter Password"

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

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

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

Me.Label1.Caption = ""
End Sub

=======
I also put this code in a general workbook that would show the userform:

Option Explicit
Sub UnprotectLots()
UserForm1.Show
End Sub



Tanya wrote:

Hi Chip
I am not very experienced in using forms or vba, and have created a basic
form called frmPassword and changed the PasswordChar property of the textbox
to the '*' character.
My question now is how do I set up the code for this? I have created a
commandbutton called CmdOK, below is the code I am using: Now I am presuming
I need to change the following code line

myPwd = Application.InputBox("Please enter correct password")
TO
myPwd = frmPassword

THEN
Does the code below become the code for the CmdOK and my original command
button needs a new code which will prompt frmPassword?

Function UnProtect_Workbook() As Boolean
'Unprotect workbook
Dim ws As Worksheet
Dim myPwd As String
Application.ScreenUpdating = False
Set ws = Worksheets(1)
On Error Resume Next
Do
myPwd = Application.InputBox("Please enter correct password")
If myPwd Then 'OK clicked
ws.Unprotect Password:=myPwd
If ws.ProtectContents Then
MsgBox "Invalid password, try again", vbOKOnly +
vbInformation, "Password input"
End If
Else 'Cancel clicked
Exit Function 'UnProtect_Workbook still set to False as this is
Default
End If
Loop Until Not ws.ProtectContents
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect Password:=myPwd
End If
Next ws
ActiveWorkbook.Unprotect Password:=myPwd
Application.ScreenUpdating = True
UnProtect_Workbook = True
End Function

Kind Regards
Tanya

--

Dave Peterson

Prompt for password
 

I added a label to the userform.

Did you?

And did you call it Label1?

ps. I had an extra line that should be deleted.

Remove that "on error resume next" line from this group.

wbUnprotected = True
On Error Resume Next



Tanya wrote:

Thank you for this Dave

I have followed your instructions, changing names where necessary i.e.
CommandButton1 and CommandButton2 around the other way, since I had already
created them.

The only problem I am having is an error Compile error Method or data member
not found

The line it refers to is Me.Label1.Caption = myStr

I tried chaning Me.Label1.Caption to Me.txtUnprotect.Caption because I had
renamed the text box to txtunprotect

Obviously I am still doing something wrong. Could you please advise.

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
On Error Resume Next
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.txtUnprotect.Caption = myStr
Application.ScreenUpdating = True
Me.txtUnprotect.Caption = ""

End Sub

Kind Regards
Tanya

"Dave Peterson" wrote:

I'm not Chip, but I created a small userform with 2 buttons, a textbox and a
label and used this code behind it:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_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.TextBox1.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
On Error Resume Next
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

End Sub
Private Sub TextBox1_Change()
Me.CommandButton2.Enabled = CBool(Len(Me.TextBox1.Value) 0)
End Sub
Private Sub UserForm_Initialize()

Me.Caption = "Enter Password"

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

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

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

Me.Label1.Caption = ""
End Sub

=======
I also put this code in a general workbook that would show the userform:

Option Explicit
Sub UnprotectLots()
UserForm1.Show
End Sub



Tanya wrote:

Hi Chip
I am not very experienced in using forms or vba, and have created a basic
form called frmPassword and changed the PasswordChar property of the textbox
to the '*' character.
My question now is how do I set up the code for this? I have created a
commandbutton called CmdOK, below is the code I am using: Now I am presuming
I need to change the following code line

myPwd = Application.InputBox("Please enter correct password")
TO
myPwd = frmPassword

THEN
Does the code below become the code for the CmdOK and my original command
button needs a new code which will prompt frmPassword?

Function UnProtect_Workbook() As Boolean
'Unprotect workbook
Dim ws As Worksheet
Dim myPwd As String
Application.ScreenUpdating = False
Set ws = Worksheets(1)
On Error Resume Next
Do
myPwd = Application.InputBox("Please enter correct password")
If myPwd Then 'OK clicked
ws.Unprotect Password:=myPwd
If ws.ProtectContents Then
MsgBox "Invalid password, try again", vbOKOnly +
vbInformation, "Password input"
End If
Else 'Cancel clicked
Exit Function 'UnProtect_Workbook still set to False as this is
Default
End If
Loop Until Not ws.ProtectContents
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect Password:=myPwd
End If
Next ws
ActiveWorkbook.Unprotect Password:=myPwd
Application.ScreenUpdating = True
UnProtect_Workbook = True
End Function

Kind Regards
Tanya

--

Dave Peterson


--

Dave Peterson

Prompt for password
 

Thanks Dave,

I didn't have a label on the form and was confused... Created a label called
label1, now I get the same error but this time it refers to PasswordChar line
as indicated below.

I din't put in a label initially because I didn't feel it was needed, can I
simply remove all trace of reference to label or does it need to be there?

Private Sub UserForm_Initialize()

Me.Caption = "Enter Administrator Password"

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

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

With Me.Label1
.SetFocus
.PasswordChar = "*" <<<<<<<<-----------
End With

End Sub

Prompt for password
 

Hello Dave
Thank you for your patience.

1. I have finally gotten the code to the stage that I am not getting any
errors. BUT nothing happens when I select OK [CommandButton1]

2. I would also appreciate your explaining the following code:

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

3. I cannot see where the code below calls on Private Sub
txtUnprotect_Change()

Could this be the problem?

4. I have placed a few questions throughout the code and would be extremely
grateful if you could give me some idea as to what the code is doing.

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 'What does eCtr refer to? <<<<<<<<<
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 'What is 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 = ""

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.CommandButton1
.Caption = "OK"
.Cancel = True
.Enabled = True
End With

With Me.CommandButton2
.Caption = "Cancel"
.Default = True
.Enabled = True 'I changed this value to True because the button was
greyed out<<<<<<<<<<<<<<<
End With

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

Me.Label1.Caption = ""
End Sub


Private Sub CommandButton2_Click()
Unload Me
End Sub

Prompt for password
 

Dave!
Thank you soo much for all your support. I finally worked out what I had
missed. The code was missing the line


Unload Me


Therefore I didn't think it was working. At last.....

I am so greatful for your support, I have been reading your code and trying
to decifer it to fully inderstand it. there are still a couple of lines of
code i don't undestand, but I have definately learnt a lot from this
experience.

Kindest Regards
Tanya


"Dave Peterson" wrote:

I added a label to the userform.

Did you?

And did you call it Label1?

ps. I had an extra line that should be deleted.

Remove that "on error resume next" line from this group.

wbUnprotected = True
On Error Resume Next



Tanya wrote:

Thank you for this Dave

I have followed your instructions, changing names where necessary i.e.
CommandButton1 and CommandButton2 around the other way, since I had already
created them.

The only problem I am having is an error Compile error Method or data member
not found

The line it refers to is Me.Label1.Caption = myStr

I tried chaning Me.Label1.Caption to Me.txtUnprotect.Caption because I had
renamed the text box to txtunprotect

Obviously I am still doing something wrong. Could you please advise.

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
On Error Resume Next
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.txtUnprotect.Caption = myStr
Application.ScreenUpdating = True
Me.txtUnprotect.Caption = ""

End Sub

Kind Regards
Tanya

"Dave Peterson" wrote:

I'm not Chip, but I created a small userform with 2 buttons, a textbox and a
label and used this code behind it:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_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.TextBox1.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
On Error Resume Next
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

End Sub
Private Sub TextBox1_Change()
Me.CommandButton2.Enabled = CBool(Len(Me.TextBox1.Value) 0)
End Sub
Private Sub UserForm_Initialize()

Me.Caption = "Enter Password"

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

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

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

Me.Label1.Caption = ""
End Sub

=======
I also put this code in a general workbook that would show the userform:

Option Explicit
Sub UnprotectLots()
UserForm1.Show
End Sub



Tanya wrote:

Hi Chip
I am not very experienced in using forms or vba, and have created a basic
form called frmPassword and changed the PasswordChar property of the textbox
to the '*' character.
My question now is how do I set up the code for this? I have created a
commandbutton called CmdOK, below is the code I am using: Now I am presuming
I need to change the following code line

myPwd = Application.InputBox("Please enter correct password")
TO
myPwd = frmPassword

THEN
Does the code below become the code for the CmdOK and my original command
button needs a new code which will prompt frmPassword?

Function UnProtect_Workbook() As Boolean
'Unprotect workbook
Dim ws As Worksheet
Dim myPwd As String
Application.ScreenUpdating = False
Set ws = Worksheets(1)
On Error Resume Next
Do
myPwd = Application.InputBox("Please enter correct password")
If myPwd Then 'OK clicked
ws.Unprotect Password:=myPwd
If ws.ProtectContents Then
MsgBox "Invalid password, try again", vbOKOnly +
vbInformation, "Password input"
End If
Else 'Cancel clicked
Exit Function 'UnProtect_Workbook still set to False as this is
Default
End If
Loop Until Not ws.ProtectContents
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect Password:=myPwd
End If
Next ws
ActiveWorkbook.Unprotect Password:=myPwd
Application.ScreenUpdating = True
UnProtect_Workbook = True
End Function

Kind Regards
Tanya

--

Dave Peterson


--

Dave Peterson

Prompt for password
 

Glad you got it working...

#2. That txtUnprotecte_Change routine checks to see if you have entered
anything in the textbox. If it's empty, then keep commandbutton2 disabled.

if len(me.txtunprotect.value) 0 then
me.commandbutton2.enabled = true
else
me.commandbutton2.enabled = false
end if

would be another way to code it.

This way, the user can't click the ok without specifying some password.

And since you swapped the commandbuttons around, the code would have to be
reversed, too:

With Me.CommandButton1
.Caption = "OK"
.Cancel = True
.Enabled = False 'make them enter something first before they
'can click ok
End With

With Me.CommandButton2
.Caption = "Cancel"
.Default = True
.Enabled = True 'yep. Cancel should be enabled.
End With

eCtr is my ErrorCounter. Each time unprotecting a sheet fails, I increment that
number. When all the sheets have been processed, I look at ectr. If it's 0,
then everything is ok. If it's non-zero, then at least one sheet is still
protected.

vblf is a linefeed character. Alt-enter does the same thing in a cell. It
results in multiple lines in the label.

========
Ps. You may want to use "Unload me" only if there are no errors. The warning
message on the label would be missed if you unload it too quickly.

Tanya wrote:

Hello Dave
Thank you for your patience.

1. I have finally gotten the code to the stage that I am not getting any
errors. BUT nothing happens when I select OK [CommandButton1]

2. I would also appreciate your explaining the following code:

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

3. I cannot see where the code below calls on Private Sub
txtUnprotect_Change()

Could this be the problem?

4. I have placed a few questions throughout the code and would be extremely
grateful if you could give me some idea as to what the code is doing.

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 'What does eCtr refer to? <<<<<<<<<
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 'What is 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 = ""

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.CommandButton1
.Caption = "OK"
.Cancel = True
.Enabled = True
End With

With Me.CommandButton2
.Caption = "Cancel"
.Default = True
.Enabled = True 'I changed this value to True because the button was
greyed out<<<<<<<<<<<<<<<
End With

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

Me.Label1.Caption = ""
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

--

Dave Peterson

Prompt for password
 

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

Prompt for password
 

'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.


Tanya wrote:

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

Prompt for password
 

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

Dave Peterson wrote:

'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.

Tanya wrote:

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

--

Dave Peterson

Prompt for password
 

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

Prompt for password
 

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

Tanya wrote:

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

Prompt for password
 

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 wrote:

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" wrote:

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

Tanya wrote:

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


--

Dave Peterson

Prompt for password
 

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" wrote:

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 wrote:

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" wrote:

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

Tanya wrote:

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


--

Dave Peterson

Prompt for password
 

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.

Tanya wrote:

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" wrote:

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 wrote:

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" wrote:

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

Tanya wrote:

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


--

Dave Peterson


--

Dave Peterson

Prompt for password
 

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

Prompt for password
 

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



Tanya wrote:

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