Making Cell protection visable (like Lotus does with different col

[PubsGirl]

In Lotus the cells that are unprotected show the font in blue (so you can see
where you've lifted security). This is only in the screen view (the font
still prints normal/black).
I can't find that Excel has any way to change the view features on
unprotected cells.
Anyone know how to get this feature (or something like it) in Excel? I
really miss it and often go back to my old version of Lotus in cases where I
really need this feature.

Thanks!

 

[Héctor Miguel]

hi, PubsGirl !

In Lotus the cells that are unprotected show the font in blue (so you can see where you've lifted security).
This is only in the screen view (the font still prints normal/black).
I can't find that Excel has any way to change the view features on unprotected cells.
Anyone know how to get this feature (or something like it) in Excel?
I really miss it and often go back to my old version of Lotus in cases where I really need this feature.

I'm sure this is not *as easy* as in 1-2-3, but [I guess] you might want to give a try w/ following 'steps'...
[assuming it is for Sheet1 and cell 'A1' can be used as 'temp' cell for _beforeprint workbook event]
-> you could also give a conditional 'blank' format or custom numeric-format to 'A1' i.e. -> ;;;

1) select 'A1' cell and define a name [insert > name > define] i.e. Unlocked
use this formula: -> =not(get.cell(14+0*now(),!a1))

2) select entire sheet' cells and use the following formual in conditional format:
=and(Unlocked,$a$1)
-> apply formats as you wish/need/prefer/...

3) copy/paste the following code in ThisWorkbook code module:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("sheet1").Range("a1") = 0
Application.OnTime Now, "RestoreCondition"
End Sub

4) copypaste the following code in a standard code module:
Option Private Module
Sub RestoreCondition()
Worksheets("sheet1").Range("a1").ClearContents
End Sub

hth,
hector.

 

[Harlan Grove]

In Lotus the cells that are unprotected show the font in blue (so
you can see where you've lifted security). This is only in the
screen view (the font still prints normal/black).

Actually this only worked in 123 when you hadn't changed font
attributes. If you had, /RU wouldn't change font color.

I can't find that Excel has any way to change the view features on
unprotected cells.
Anyone know how to get this feature (or something like it) in Excel?
I really miss it and often go back to my old version of Lotus in
cases where I really need this feature.

The simplest way to do this in Excel is to create a custom style. Run
the menu command Format > Style to display the Style dialog. Enter
Unprotected as the style name. Uncheck Number, Alignment, Border and
Pattern checkboxes. Click on the Modify... button to display the
Format Cells dialog. Click on the Font tab and change the font color
to Blue. Click on the Protection tab and uncheck the Locked checkbox.
Click on the OK button in the Format Cells dialog, then click on the
OK button in the Styles dialog.

The limitation on this approach is that the typeface, point size and
other font attributes are included in the Unprotected style. For this
reason, I've found it easier to set the cell background to light blue
rather than the font color to blue.

Another limitation: font color or cell background color will appear in
printout. Unless you want to use a BeforePrint event handler to change
printout appearance from on-screen appearance, you're stuck with
WYSIWYG in Excel.

 

[PubsGirl]

Thanks, that may be helpful. But I'm not sure it is worth that much time.
It sure is a lot faster to just use Lotus, which by the way, can do lots of
other things more efficiently than Excel too.

Thanks for your help!

hi, PubsGirl !

In Lotus the cells that are unprotected show the font in blue (so you can see where you've lifted security).
This is only in the screen view (the font still prints normal/black).
I can't find that Excel has any way to change the view features on unprotected cells.
Anyone know how to get this feature (or something like it) in Excel?
I really miss it and often go back to my old version of Lotus in cases where I really need this feature.

I'm sure this is not *as easy* as in 1-2-3, but [I guess] you might want to give a try w/ following 'steps'...
[assuming it is for Sheet1 and cell 'A1' can be used as 'temp' cell for _beforeprint workbook event]
-> you could also give a conditional 'blank' format or custom numeric-format to 'A1' i.e. -> ;;;

1) select 'A1' cell and define a name [insert > name > define] i.e. Unlocked
use this formula: -> =not(get.cell(14+0*now(),!a1))

2) select entire sheet' cells and use the following formual in conditional format:
=and(Unlocked,$a$1)
-> apply formats as you wish/need/prefer/...

3) copy/paste the following code in ThisWorkbook code module:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("sheet1").Range("a1") = 0
Application.OnTime Now, "RestoreCondition"
End Sub

4) copypaste the following code in a standard code module:
Option Private Module
Sub RestoreCondition()
Worksheets("sheet1").Range("a1").ClearContents
End Sub

hth,
hector.

 

[PubsGirl]

Thanks for your help. I guess there just isn't an easy solution. Once
again I find that Microsoft makes everything more labor intensive that it
needs to be.

 

[Dave Peterson]

If you're not using Format|conditional formatting, you could try using that:

Select the range you want shaded (all the cells on the sheet????)

I chose A1:G99 in my sample
and with A1 the activecell
format|conditional|formatting
formula is:
=(CELL("Protect",A1)=0)
and give it a nice shade.

If you to hide that shading sometimes, you could pick out a cell and include
that in the conditional formatting formula. I used X1.

Same rules (A1 the active cell of the selection):
formula is:
=AND((CELL("Protect",A1)=0),$X$1<>"")

So when I put anything in X1, I see the shading. If I want to print (say)
without the shading, I can just clear X1.