Protect/Unprotect worksheet VBA BUTTON?

[s_macloskey]

Hello all,

context:

I have created an employee holiday/leave sheet on (we use Excel 365)

I have made a sheet for each month and they require 'protecting' and 'unprotecting' seperately/individually - with a password

query:


My team is not so computer literate so, I need to create an easy to use Marco/VBA Button(s) which will protect (and unprotect) the worksheet and prompt for a password, the same way it would if you were to go to "Review, Protect worksheet"

I have tried creating a button and using VBA, and I've had a tiny amount success but I'm new to VBA and it's a bit of a mess.

*I've attached a screenshot for reference*

Can anyone provide any assistance?

Thanks,

S Macloskey

 

[davesexcel]

You can change the sheet to active sheet or whatever the sheet name you want to unprotect
Set sh = activesheet
Set sh = Sheets("MySheet")

Sub Unprotect_Sh()
    Dim sh As Worksheet
    Dim pw As String
    Dim inptBx As String

    Set sh = Sheets(2)
    pw = "1234"
    With sh
        inptBx = InputBox("Enter Pasword")
        If inptBx = pw Then
            .Unprotect pw
        Else
            MsgBox "Wrong Pasword"
        End If
    End With



End Sub

 

[Yongle]

You could use an active-x toggle button placed on the sheet to be protected
This alters the caption on the button when clicked

Private Sub ToggleButton1_Click()
    Dim toggle As Object:   Set toggle = Me.ToggleButton1
    Const pw = "Password"
    If InputBox("Enter password", "") = pw Then
        Select Case toggle.Value
            Case True
                toggle.Caption = "Protect"
                Me.Unprotect pw
            Case False
                toggle.Caption = "Unprotect"
                Me.Protect pw
            End Select
    Else
        MsgBox "Bad password"
    End If
End Sub

Also consider:
The user may forget to protect the sheet
Perhaps the sheet should be auto-protected when user activates a different sheet or saves the workbook
If you need help with that, let us know

 

[s_macloskey]

You could use an active-x toggle button placed on the sheet to be protected
This alters the caption on the button when clicked

Private Sub ToggleButton1_Click()
    Dim toggle As Object:   Set toggle = Me.ToggleButton1
    Const pw = "Password"
    If InputBox("Enter password", "") = pw Then
        Select Case toggle.Value
            Case True
                toggle.Caption = "Protect"
                Me.Unprotect pw
            Case False
                toggle.Caption = "Unprotect"
                Me.Protect pw
            End Select
    Else
        MsgBox "Bad password"
    End If
End Sub

Also consider:
The user may forget to protect the sheet
Perhaps the sheet should be auto-protected when user activates a different sheet or saves the workbook
If you need help with that, let us know

Wow! Thank you so much for this. It works perfectly.
Auto-protecting when switching between sheets would be a good idea too and I would need help with that, nonetheless this is the perfect solution.

Thank you,

S Macloskey

 

[Yongle]

Auto-protecting when switching between sheets would be a good idea

Code below auto-protects the sheet when user clicks on a different sheet
It must be placed in the sheet's own code window as detailed below

Right click on sheet tab \ View Code \ paste code below into the window that opens \ amend the password

Private Sub Worksheet_Deactivate()
    Me.Protect "Password"
End Sub

 

[s_macloskey]

Code below auto-protects the sheet when user clicks on a different sheet
It must be placed in the sheet's own code window as detailed below

Right click on sheet tab \ View Code \ paste code below into the window that opens \ amend the password

Private Sub Worksheet_Deactivate()
    Me.Protect "Password"
End Sub

This works very well, thank you Yongle. That's really helpful.