Protection Question

[dcompagnone]

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

 

[Domski]

Hi,

Something like this:

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]

Something like this?

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]

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]

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]

Maybe:

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]

doofusboy,

Apologies, actually, I got that wrong.

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


Dom

 

[dcompagnone]

Domski,

Gold medal!

Thank you very much guys!

Bootiful!

Dom

 

[doofusboy]

"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?