Protect/Unprotect worksheet VBA BUTTON?

s_macloskey

New Member
Joined
Jul 1, 2020
Messages
7
Office Version
365
Platform
Windows
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
 

Attachments

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
815
You can change the sheet to active sheet or whatever the sheet name you want to unprotect
Set sh = activesheet
Set sh = Sheets("MySheet")

VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,085
Office Version
365
Platform
Windows
You could use an active-x toggle button placed on the sheet to be protected
This alters the caption on the button when clicked
VBA Code:
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

New Member
Joined
Jul 1, 2020
Messages
7
Office Version
365
Platform
Windows
You could use an active-x toggle button placed on the sheet to be protected
This alters the caption on the button when clicked
VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,085
Office Version
365
Platform
Windows
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
VBA Code:
Private Sub Worksheet_Deactivate()
    Me.Protect "Password"
End Sub
 

s_macloskey

New Member
Joined
Jul 1, 2020
Messages
7
Office Version
365
Platform
Windows
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
VBA Code:
Private Sub Worksheet_Deactivate()
    Me.Protect "Password"
End Sub
This works very well, thank you Yongle. That's really helpful.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,028
Messages
5,472,089
Members
406,800
Latest member
TeachMeExcelPlease

This Week's Hot Topics

Top