Protection Question

dcompagnone

Board Regular
Joined
Dec 14, 2007
Messages
119
Hi Professionals!

I want to run a simple macro that enables me to unlock and lock all sheets at once with the same password through a single userform BUT that also requires that I put another form of identification in before it will run.

Simply, I have created a userform that asks me for two pieces of information, a pin number and a password.

I would like the pin number to be validated before the password is applied.

something along the lines of

PIN = 5555
Password = secret
'if textbox1.Value = "5555" and textbox2.value = "secret" then protect/unprotect all sheets using password.

Else MsgBox "Either your PIN or Password do not match, please check and try again."

I'd like the same code to run for protecting and unprotecting .

Help appreciated

Dominic
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

Something like this:

Code:
Dim wsEachSheet As Worksheet

If textbox1.Value <> "5555" Or textbox2.Value <> "secret" Then

    MsgBox "Either your PIN or Password do not match, please check and try again."
    Exit Sub

Else

    For Each wsEachSheet In ThisWorkbook.Worksheets

        wsEachSheet.Unprotect Password:="secret"

    Next wsEachSheet

End If

Dom
 

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
Something like this?

HTML:
If Textbox1.Value = "5555" And Textbox2.Value = "secret" Then
    For Each Worksheet In Worksheets
        If Worksheet.ProtectionMode = False Then
            Worksheet.Protect Textbox2.Value
        Else
            Worksheet.Unprotect Textbox2.Value
        End If
    Next Worksheet
Else
    MsgBox "Either your PIN or Password do not match, please check and try again.", vbExclamation, "Invalid Entry"
End If
 

dcompagnone

Board Regular
Joined
Dec 14, 2007
Messages
119
Domski,

That's pretty much there. But from what I can see, that will only unlock it if it is locked.

Can I get this to lock and unlock please??

Dom
 

dcompagnone

Board Regular
Joined
Dec 14, 2007
Messages
119

ADVERTISEMENT

doofusboy,

Thanks for your reply too but that only unlocks a locked sheet. it doesn't do lock and unlock.

Suggestions please gentlemen - you're very close!

Thanks

Dom
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Maybe:

Code:
Dim wsEachSheet As Worksheet

If textbox1.Value <> "5555" Or textbox2.Value <> "secret" Then

    MsgBox "Either your PIN or Password do not match, please check and try again."
    Exit Sub

Else

    For Each wsEachSheet In ThisWorkbook.Worksheets

        If ws.ProtectContents = True Then
        
            wsEachSheet.Unprotect Password:="secret"
            
        Else
        
            wsEachSheet.Protect Password:="secret"
            
        End If

    Next wsEachSheet

End If

Dom
 

dcompagnone

Board Regular
Joined
Dec 14, 2007
Messages
119

ADVERTISEMENT

doofusboy,

Apologies, actually, I got that wrong.

Your code lucks but doesn't unlock - any ideas?


Dom
 

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
"Your code lucks but doesn't unlock - any ideas?"

The only thing I can think of is that it will not Unlock with a password something that was never locked with a password to begin with? Maybe if worksheets wwere manually protected with the password first the code would work?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,567
Messages
5,659,580
Members
418,509
Latest member
empk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top