Auto re-protect

[cnicks17]

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]

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]

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]

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]

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

 

[Jimmypop]

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