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

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

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

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

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

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

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

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

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

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

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

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

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