# Auto re-protect



## cnicks17 (Tuesday at 8:21 PM)

Hello!

I am fairly new at messing with Excel beyond the very basics. I have a schedule at work that I would like a specific worksheet to require a password each time it is opened. Sadly, the problem is that using the build in protection in Excel, some of the individuals who require access to view/edit this worksheet forget to re-protect the sheet. 

I was hoping there was a way to have the sheet auto-protect and require a password each time that sheet is clicked on. Any suggestions would be much appreciated! 

Thank you!


----------



## aaewalsh (Wednesday at 5:38 AM)

add this to the worksheet you want it password protected:

```
Private Sub Worksheet_Activate()
    Me.Protect Password:="yourpassword", UserInterfaceOnly:=True
End Sub
```

You can also create a macro that run on open, that will check if the sheet is protected, if not it will protect it.

```
Private Sub Workbook_Open()
    If Not Me.Sheets("Sheet1").ProtectContents Then
        Me.Sheets("Sheet1").Protect Password:="yourpassword"
    End If
End Sub
```


----------



## Jimmypop (Wednesday at 5:57 AM)

Hi there 

You can try the following vba approach maybe...


```
Private Sub Worksheet_Activate()
    Dim pass        As String
    pass = InputBox("Password?")
    If StrPtr(pass) = 0 Then
        MsgBox "User cancelled!", vbCritical, "Admin"
    ElseIf pass = vbNullString Then
        MsgBox "Please enter correct password!", vbCritical, "Admin"
    Else
        On Error GoTo Popup:
        ActiveSheet.Unprotect pass
        Exit Sub
        Popup:
        If Err.Number = 1004 Then
            MsgBox "Incorrect Password!", vbCritical, "Admin"
        End If
    End If
End Sub
Private Sub Worksheet_Deactivate()
    Worksheets("Sheet1").Protect "qwe"
End Sub
```


----------



## shinigamilight (Wednesday at 6:38 AM)

```
Private Sub Worksheet_Activate()
        Protect "pass123"
End Sub
```
This is all you need, just go to the sheet and click on worksheet activate event and paste this.


----------



## cnicks17 (Wednesday at 6:41 AM)

Thank you both! These approaches both worked! Jimmy's addition of the pop-up Simplified my idea even further, thank you!


----------



## Jimmypop (Wednesday at 7:16 AM)

Glad you got sorted and glad we could assist....


----------

