input pword into textbox to unprotet sheet

[harveya915]

I have a button assigned to open up a UserForm with a text box. I sort of have it set up so that when you input the unprotect pword in to that text box, then it unprotects the sheet. However I have an issue that when the incorrect pword is entered it gives me a run time error. Can someone help me out with this? below is the code I am using.

Private Sub CommandButton7_Click()
Dim wSheet As Worksheet
Dim pword As String
pword = "2019"
 
If pword <> TextBox2.Text Then MsgBox "Invalid Password"
For Each wSheet In Worksheets
If wSheet.ProtectContents = True And TextBox2.Text = 2019 Then
wSheet.Unprotect Password:=2019
Else
[COLOR=#ff0000]wSheet.Unprotect Password:=TextBox2.Text[/COLOR]
End If
Next wSheet
Unload UserForm3

End Sub

The debugger highlights the row that is in red.

Just a little heads up, I am new to this VBA. I found this code somewhere on here, but tweaked it a bit so that it meets my needs. So some things may seem impractical where I use them.

 

[DanteAmor]

Try this:

Private Sub CommandButton7_Click()
Dim wSheet As Worksheet
Dim pword As String
pword = "2019"
 
On error resume next
If pword <> TextBox2.Text Then MsgBox "Invalid Password"
For Each wSheet In Worksheets
If wSheet.ProtectContents = True And TextBox2.Text = 2019 Then
wSheet.Unprotect Password:=2019
Else
wSheet.Unprotect Password:=TextBox2.Text
End If
Next wSheet
Unload UserForm3


End Sub

Or validate the password

Private Sub CommandButton7_Click()
Dim wSheet As Worksheet
Dim pword As String
pword = "2019"
 
If pword <> TextBox2.Text Then MsgBox "Invalid Password"
For Each wSheet In Worksheets
If wSheet.ProtectContents = True And TextBox2.Text = 2019 Then
wSheet.Unprotect Password:=2019
Else
if textbox2.value = "2019" then
   wSheet.Unprotect Password:=TextBox2.Text
end if
End If
Next wSheet
Unload UserForm3


End Sub

 

[harveya915]

Thank you very much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[harveya915]

On a separate note. When I protect the sheet, can i have a cell turn in to a certain color (red), and then when it is unprotected for it to turn green? I also have a button that will protect the sheet. That cell would be A1.

 

[DanteAmor]

Check this example:

Sub ProtectSheet()
    Sheets("Sheet1").Unprotect "2019"
    Range("A1").Interior.ColorIndex = 3
    Sheets("Sheet1").Protect "2019"
End Sub


Sub UnProtectSheet()
    Sheets("Sheet1").Unprotect "2019"
    Range("A1").Interior.ColorIndex = 4
End Sub

 

[harveya915]

Where would I put this code? I've tried under "Sheet1" and under "thisworkbook". it doesn't work in either. I even tried in the UserForms for the buttons mentioned previously.

Thanks for the help!

 

[GirishDhruva]

Check with this

Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
Dim pword As String
pword = "2019"
 
For Each wSheet In Worksheets
If TextBox1.Text = 2019 Then
    wSheet.Unprotect Password:=pword
    Range("A1").Interior.ColorIndex = 4
    MsgBox "valid Password"
Else
    wSheet.Unprotect Password:=pword
    Range("A1").Interior.ColorIndex = 3
    wSheet.Protect Password:=pword
    MsgBox "Invalid Password"
End If
Next wSheet
Unload UserForm1
End Sub

If the sheet is Prior protected

Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
Dim pword As String
pword = "2019"
 
For Each wSheet In Worksheets
If wSheet.ProtectContents = True And TextBox1.Text = 2019 Then
    wSheet.Unprotect Password:=pword
    Range("A1").Interior.ColorIndex = 4
    MsgBox "valid Password"
Else
    wSheet.Unprotect Password:=pword
    Range("A1").Interior.ColorIndex = 3
    wSheet.Protect Password:=pword
    MsgBox "Invalid Password"
End If
Next wSheet
Unload UserForm1
End Sub

Where would I put this code? I've tried under "Sheet1" and under "thisworkbook". it doesn't work in either. I even tried in the UserForms for the buttons mentioned previously.

Thanks for the help!

 

[DanteAmor]

Where would I put this code? I've tried under "Sheet1" and under "thisworkbook". it doesn't work in either. I even tried in the UserForms for the buttons mentioned previously.

Thanks for the help!

In a module.

On your sheet create one button to protect and another to check out.