Custom Message in Cell A1 when Workbook unprotected?

chandelirious

Board Regular
Joined
Sep 9, 2004
Messages
84
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!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try this:
VBA Code:
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:
VBA Code:
Range("A1:G1").Clear
 
Upvote 0
Try this:
VBA Code:
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:
VBA Code:
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?
 
Upvote 0
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.

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top