Custom Message in Cell A1 when Workbook unprotected?

[chandelirious]

This is a new question, based on a previous question of mine that I couldn't find a solution for!!

I currently work on a large spreadsheet that is regularly used and worked on simultaneously by many people at once.

The majority of those working with it are not the most technologically minded people. As such, the worksheet is locked down quite heavily so that only certain areas can be edited by them.

However, when I'm in it - and it's a lot, and for long periods at a time - I need to unprotect the worksheet. During that time, others are working on it, and they frequently accidentally do things. I'm talking hiding columns, removing formulas, etc. They claim that they haven't done it, because they don't have access to the unprotected sheet. So I get the blame.

Ideally, I'd love to be able to say to them something along the lines of "You can't access the spreadsheet between 2.00 and 3.00pm every day," but that just won't work. Also, whereas I'm an office 9-5 worker, some of these are not, and will be accessing it out of office, so it's not even as if I can stay behind once a week to catch up.

What I do now is unprotect the workbook, and then merge Cells A1:G1, fill them red, with white text that says: WARNING: WORKBOOK UNPROTECTED

I know that at least two other people have the password, and, although they claim not to unlock it, I'm sure that they do (and possibly forget to lock it again).

Is there a way, perhaps using VBA, that as soon as the workbook is unprotected, Cells A1:G1 auto format to display my warning message?

Thanks in advance!

 

[portews]

Try this:

If Worksheets(1).ProtectContents = False Then
    With Range("A1:G1")
         .Merge
         .Interior.Color = vbRed
         .Font.Color = vbWhite
         .HorizontalAlignment = xlHAlignCenter
         .BorderAround Weight:=xlThick
         .Value = "WARNING: WORKBOOK UNPROTECTED"
    End With
End If

to reset it:

Range("A1:G1").Clear

 

[chandelirious]

Try this:

If Worksheets(1).ProtectContents = False Then
    With Range("A1:G1")
         .Merge
         .Interior.Color = vbRed
         .Font.Color = vbWhite
         .HorizontalAlignment = xlHAlignCenter
         .BorderAround Weight:=xlThick
         .Value = "WARNING: WORKBOOK UNPROTECTED"
    End With
End If

to reset it:

Range("A1:G1").Clear

Thanks very much for this - however, I've tried it, and nothing happens when I protect / unprotect.

What I've tried:

Saved Workbook as Macro-enabled first.
Opened VBA.
Insert Module.
(Also tried Insert Class Module)
Then tried to Protect Sheet, but no 'error message' appeared. Also tried Protect Workbook, but again, nothing changed. Perhaps I'm missing a step?

 

[portews]

There isn't an easy trigger for protecting and unprotecting a sheet, so to get what you want, you can do one of a few things.

The easiest way would be to have it trigger when you change cells. The downside is that it won't trigger until you change cells.
Add the code above in the code page for the specific worksheet you want to check for (not a separate module) under "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" Then it will check everytime you click in a new cell to see if the worksheet is protected or not.

You can also include the unprotect in with this code and run it manually. This will toggle back and forth between protect and unprotect. If you have a password, you'll need to unrem the first comment and change the password text. This you can put in a module and run it from the macros.

Sub UnprotectMe()
If Worksheets(1).ProtectContents = True Then
    Worksheets(1).Unprotect  'Password:="myPassword" 'if needed
    With Range("A1:G1")
         .Merge
         .Interior.Color = vbRed
         .Font.Color = vbWhite
         .HorizontalAlignment = xlHAlignCenter
         .BorderAround Weight:=xlThick
         .Value = "WARNING: WORKBOOK UNPROTECTED"
    End With
Else
Range("A1:G1").Clear
Worksheets(1).Protect 'Password:="myPassword" ' and other options, if needed
End Sub

Or you can have a timer to check every so often, but that seems like overkill to me.