1

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

aynber
  • 22,380
  • 8
  • 50
  • 63
JohnM
  • 37
  • 1
  • 2
  • 9

2 Answers2

1

Put this in the worksheet's code module, which will place a reminder in the Status Bar (this avoids needing to lock/unlock the sheet in order to write the status in to cell A1).

Put this in Sheet1 code module. The macro will execute every time sheet1 is activated.

Private Sub Worksheet_Activate()
If ActiveSheet.ProtectContents then
    Application.StatusBar = "This sheet is protected"
Else:
    Application.StatusBar = "This sheet is unprotected"
End If
End Sub

Private Sub Worksheet_Deactivate()
    Application.StatusBar = False
End Sub

To protect/unprotect the worksheet you could add this to an Insert>Module. Then attach these macros to separate command buttons, or run from the Developer>Macros ribbon.

Const myPassword as String = "password" '<-- replace "password" with your password

Sub Sht1Protect()
    Sheet1.Protect myPassword
End Sub

Sub Sht1Unprotect()
    Sheet1.Unprotect myPassword
End Sub

To ensure the sheet is always protected when you close the file, insert this in the Workbook code module

Private Sub Workbook_Close()
    Sht1Protect
End Sub

You may need additional handling to control whether the file is saved/not saved etc.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Thanks David - some noob questions...Am i putting this in to the sheet1's code module or going insert>module? I've put it in both and stepped through it in the VBA editor. Can it be linked to a button as a macro or do I use the excel built-in protect worksheet toggle? Can I get it to automatically update to protected/unprotected when done? – JohnM Mar 20 '13 at 03:02
  • Hi @JohnM, see my revision. – David Zemens Mar 20 '13 at 04:21
  • Thanks David, while this works, for my implementation I'd prefer to be able to format a cell to make it's status more obvious to the user. – JohnM Mar 20 '13 at 22:23
  • well then brettdj's answer does that. next time it would be nice if you don't send me on a wild goose chase exploring questions/options that you're not actually interested in. cheers. – David Zemens Mar 21 '13 at 01:17
  • Wow. Sorry you feel that way. As a new user to both this site and Excel VBA code I was approaching everything with an open mind and explored your solution in detail to see if it could fit with what I was doing. No offense was intended. – JohnM Mar 21 '13 at 16:25
  • Next time someone gives you several well-documented code examples, responds to your follow-up queries, is willing to take their time to explore several options for your particular problem, and modifies their answer(s) per your requests, and you find this information in **any** way helpful (even if you ultimately choose a different solution) you might consider **at least** giving their answer an "upvote", for helping you out. – David Zemens Mar 21 '13 at 16:47
  • John no worries, sorry if I came off a little gruff in my previous comment. – David Zemens Mar 21 '13 at 17:05
1

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
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • Thanks, brettdj - this works to suit my requirements. Does using activex controls have any influence over VBA code that is used with them? – JohnM Mar 20 '13 at 22:24