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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

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?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,330
Messages
5,510,653
Members
408,806
Latest member
Hunlight

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top