Protect/Unprotect Sheet with a checkbox

Copper Contributor

Can anyone help please, CHAT GPT can't!!

I'm trying to record a macro to Protect/Unprotect a single sheet using a Checkbox in Excel 365 for Mac but am unable to mage it.

All suggestions warmly welcomed... :) 

Thanks in anticipation....

6 Replies

@Mick_Kitcher 

Does the attached workbook work on a Mac? The macro assigned to the check box is:

Sub CheckBox1_Click()
    With Sheet1
        If .Shapes("Check Box 1").ControlFormat.Value = 1 Then
            .Protect ' Password:=:"Secret"
        Else
            .Unprotect ' Password:="Secret"
        End If
    End With
End Sub

Since it is a macro-enabled workbook, make sure that you allow macros when you open it.

Hi Hans (again) before i try it i should have mentioned that i don't want to use a password, do i just ignore it or omit from the code? My sheet is named DATA by the way. :)

Thanks

@Mick_Kitcher 

The password has been commented out in the code.

You'll have to replace Sheet1 with Worksheets("Data") in the code, and "Check Box 1" with the name of your check box.

@Hans Vogelaar 

Hi Hans, i just can't seem to get it to work, sorry! I've attached my file if you wouldn't mind looking to see where i'm going wrong, i'm trying to get it running ok for my daughter who starts her new job on Monday so any help, as always, is greatly appreciated.

@Mick_Kitcher 

See the attached workbook.

  • The code is now in Module4 instead of in a worksheet module.
  • It uses Worksheets("DATA") instead of DATA.
  • It uses "Check Box 8" since that is the name of your check box.
Hans, you're a star, thank you so much for getting me out of the dirt yet again, it's brilliant!!:clapping_hands: