?
Solved

Conditional format depending on protection

Posted on 2011-05-10
5
Medium Priority
?
186 Views
Last Modified: 2012-05-11

This is not a must have, but I would like to be able to set the format of a cell dependant on whether ther Worksheet has protection turned on.

I had hoped to be able to do this via Conditional Formatting - but cannot fugure out a syntax for the formula to use.

Any suggestions gratefully received.

Regards

Richard Tomalin
0
Comment
Question by:rltomalin
  • 3
5 Comments
 
LVL 2

Assisted Solution

by:balatheexpert
balatheexpert earned 400 total points
ID: 35729160
no dude!

u cant do that!
0
 
LVL 19

Accepted Solution

by:
Arno Koster earned 1600 total points
ID: 35729242
the fastest way to test if a worksheet has protection turned on is to implement a VBA code module with the following macro in it :
Public Function isProtected() As Boolean
    isProtected = ActiveSheet.ProtectScenarios
End Function

Open in new window


you can test this functionality by entering the formula

"=isProtected()"

in a cell somewhere in a worksheet. Then open the properties of this cell and de-activate the blocking protection.
activate worksheet protection and manually update the cell to refresh the value of the formula.

0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35729603
The outcome of the formula can be used as an input to determine conditional formatting, but there is one drawback : activating or de-activating sheet protection does not fire an event that can be handled within VBA as far as i know. So you would have to either manually refresh the formula in order for the correct value to show, or use / abuse another event macro to do this for you (eg. events such as sheet selected / cell selected / sheet changed or make a timed refresh macro which runs every second or so)
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35729655
in that case, it might be far more easy to implement a macro which runs each time the sheet is activated :

Private Sub Worksheet_Activate()
    If ProtectScenarios Then
        Range("B2").Interior.Color = vbBlue
    Else
        Range("B2").Interior.Color = vbYellow
    End If
End Sub

Open in new window


pay attention though that the particular cell B2 must not be blocked by protection, because otherwise the blue color can not be set !
or use something like

Private Sub Worksheet_Activate()
    If ProtectScenarios Then
        Unprotect
        Range("B2").Interior.Color = vbBlue
        Protect
    Else
        Range("B2").Interior.Color = vbYellow
    End If
End Sub

Open in new window

0
 

Author Closing Comment

by:rltomalin
ID: 35756326
I gave balatheexpert 100 points because it did specifically answer the question.  However akoster did give a detailed explanation of methods that could be used to accomplish the result asked for.

Regards

Richard
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question