Protect/Unprotect Password via userform

[Donai]

hi, i have created a userform to unprotect/protect all worksheets in a workbook, so in the userform i have a textbox and a command button, in the userform code i have this, the problem is where do i put the password in this code?


Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
If wSheet.ProtectContents = True Then
wSheet.Unprotect Password:=TextBox1.Text
Else
wSheet.Protect Password:=TextBox1.Text
End If
Next wSheet
Unload Me
End Sub


</PRE>


</PRE>

 

[Smitty]

Welcome to the Board!

Why bother with a user form unless you want users to be able to interact with it? You can do it seamlessly in the code itself without a form.

You can add the password like this (not tested):

Private Sub CommandButton1_Click()
  Dim wSheet As Worksheet
  Dim pword as string
    pword = "yada"
 
    For Each wSheet In Worksheets
      If wSheet.ProtectContents = True And TextBox1.Text = pword Then
         wSheet.Unprotect Password:=pword
      Else
         wSheet.Protect Password:=TextBox1.Text
      End If
   Next wSheet
Unload Me
End Sub

 

[Donai]

ok i ran the below, but i got a run time error 1004- Password you suplied is not correct, though i get this message, when i click on a cell its locked, when i run the code again and put password in txtbox i get the same error, but the sheet is unlocked, then why do i get run-time error?

Welcome to the Board!

Why bother with a user form unless you want users to be able to interact with it? You can do it seamlessly in the code itself without a form.

You can add the password like this (not tested):

Private Sub CommandButton1_Click()
  Dim wSheet As Worksheet
  Dim pword as string
    pword = "yada"
 
    For Each wSheet In Worksheets
      If wSheet.ProtectContents = True And TextBox1.Text = pword Then
         wSheet.Unprotect Password:=pword
      Else
         wSheet.Protect Password:=TextBox1.Text
      End If
   Next wSheet
Unload Me
End Sub

 

[Donai]

Ok, i got this to work, but i came across an issue. When i unlock password via userform and then type say i randomly type ASD in the userform and press OK, then and accepts it, but when i try to use the orignal password to unlock again then it does not seem to accept it. Is there anyway round this, can we put a message box, so if the user manually inputs some random string in the textbox and then executes the command button OK then i would like the code give a warning message, "Saying incorrect password, please try again"

Private Sub CommandButton1_Click()
  Dim wSheet As Worksheet
  Dim pword As String
    pword = "yada"
 
    For Each wSheet In Worksheets
      If wSheet.ProtectContents = True And TextBox1.Text = pword Then
         wSheet.Unprotect Password:=pword
      Else
         wSheet.Protect Password:=TextBox1.Text
      End If
   Next wSheet
Unload Me
End Sub

 

[Smitty]

Right,

The problem is that there are potentially different passwords. I just chose a random password for the unprotect, which should match the protect routine (like I said, I didn't test it).

You'll need to make the password code look for and use matching passwords in each instance. You can also add a message box along these lines:

If pword <> TextBox1.Text Then MsgBox...

Sorry for being so short, but I've got to go finish tilling the garden before dark.

 

[Donai]

I get an error message End if without block if on the last End If after next

Right,

The problem is that there are potentially different passwords. I just chose a random password for the unprotect, which should match the protect routine (like I said, I didn't test it).

You'll need to make the password code look for and use matching passwords in each instance. You can also add a message box along these lines:

If pword <> TextBox1.Text Then MsgBox...

Sorry for being so short, but I've got to go finish tilling the garden before dark.

 

[Smitty]

I just posted an example of what you could do to validate the password, not necessarily what would work in your case. The ellipsis (...) indicates that's only a partial code snippet.

In the mean time feel free send me a copy of your wb and I'll see what I can do. Just PM me with your e-mail address.

 

[Donai]

I just posted an example of what you could do to validate the password, not necessarily what would work in your case. The ellipsis (...) indicates that's only a partial code snippet.

In the mean time feel free send me a copy of your wb and I'll see what I can do. Just PM me with your e-mail address.

I'm at work so unable to send email , i did try and put the msgbox at the start and it works, but again if i randomly input a string in text box, i get the msgbox message "No Pass" as expected, but then when i press ok it does not recognise the orignal password when i input in textbox, so maybe we can have a cancel button in the msg box.

Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
Dim pword As String
pword = "yada"
 
If pword <> TextBox1.Text Then MsgBox "No Pass"
For Each wSheet In Worksheets
If wSheet.ProtectContents = True And TextBox1.Text = pword Then
wSheet.Unprotect Password:=pword
Else
wSheet.Protect Password:=TextBox1.Text
End If
Next wSheet
Unload Me
End Sub

 

[Donai]

i tried using vbcancel, but still i am having issues when i randomly type the wrong password, click ignore, open the userform again, type the correct password and i get run time error 1004, password incorrect

Private Sub CommandButton1_Click()
  Dim wSheet As Worksheet
  Dim pword As String
    pword = "yada"
    
    If pword <> TextBox1.Text Then MsgBox "No Pass", vbCancel
 
    For Each wSheet In Worksheets
      If wSheet.ProtectContents = True And TextBox1.Text = pword Then
         wSheet.Unprotect Password:=pword
      Else
         wSheet.Protect Password:=TextBox1.Text
      End If
   Next wSheet
Unload Me
End Sub

 

[Smitty]

Try this:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>  <SPAN style="color:#00007F">Dim</SPAN> wSheet <SPAN style="color:#00007F">As</SPAN> Worksheet<br>  <SPAN style="color:#00007F">Dim</SPAN> pword <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    pword = "yada"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> pword <> TextBox1.Text <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "No Pass", vbCancel<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    Else:<br>         <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wSheet <SPAN style="color:#00007F">In</SPAN> Worksheets<br>           <SPAN style="color:#00007F">If</SPAN> wSheet.ProtectContents = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>              wSheet.Unprotect Password:=pword<br>           <SPAN style="color:#00007F">Else</SPAN><br>              wSheet.Protect Password:=pword<br>           <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> wSheet<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>Unload Me<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>