Prompt for password

[Tanya]

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]

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.

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

 

[Tanya]

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

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.

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

 

[Gary Keramidas]

tanya:

dave just had a typo.

try this

Dim myPwd As String

--


Gary

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

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.

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

 

[Tanya]

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

tanya:

dave just had a typo.

try this

Dim myPwd As String

--


Gary

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

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

 

[Tanya]

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

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.

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

 

[Bob Phillips]

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)

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

tanya:

dave just had a typo.

try this

Dim myPwd As String

--


Gary

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

:

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

 

[Tanya]

Thank you Bob. Worked a treat.

Much appreciated.

Kind Regards
Tanya

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)

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

tanya:

dave just had a typo.

try this

Dim myPwd As String

--


Gary


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

:

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]

Thanks for the correction, Gary.

(My brain and fingers had a disconnect!)

tanya:

dave just had a typo.

try this

Dim myPwd As String

--

Gary

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

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]

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.

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

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.

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

 

[Tanya]

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

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.

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

 

[Chip Pearson]

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)

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

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.

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

 

[Tanya]

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]

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]

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]

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

 

[Tanya]

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

 

[Tanya]

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

 

[Tanya]

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

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.

 

[Dave Peterson]

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.

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