Search code examples
excelvbaworksheetspreadsheet-protection

Pass the Protection Status of Excel Worksheet to a Cell

I'm curious as to whether it's possible to pass the protection status of an excel worksheet to a cell of that worksheet. e.g.

  • Sheet1 is locked for editing...cell A1 would be programmed to say "locked"
  • Sheet1 is unlocked...cell A1 would say "unlocked".

A button on the sheet would be used to toggle worksheet protection on and off.

My sheet will be locked upon opening using a workbook_open event.

This is for a sheet where I don't want the formulae getting all mucked up upon use, but where full access might be required. Its more as a reminder to the user that they are in "Unlocked" Mode so to be extra careful.

Is using VBA a foregone conclusion?

I'm a VBA noob but don't mind using code as a solution for this

Any thoughts or suggestions welcome

Solution

  • You could use code in an ActiveX button on Sheet1 to do this simply 

    Const strPAss = "test"

Private Sub CommandButton1_Click()
 If ActiveSheet.ProtectContents Then
 ActiveSheet.Unprotect strPAss
 [a1].Value = "unlocked"
 Else
 [a1].Value = "locked"
 ActiveSheet.Protect strPAss
 End If
End Sub