Protected Sheets - Viewing Cells that have been Unlocked

[CWEngineer]

I created and excel spreadsheet. Then I selected a few cells. I right clicked and selected "Format Cells", then I unchecked "Locked". I then Protected the sheet so that I could only change the cells that I selected intially.

I am trying to find out if there is way to view the cells in the sheet that are unlocked.

Appreciate your help.

 

[Denial]

I am not aware of any way of having Excel emphasise unlocked cells in some way, although it would be quite easy to write some VBA code to achieve this.

When I unlock a cell, I always change its colour at the same time. (I want users of my spreadsheets to be instantly aware of where they are able to enter data.) I use a subtle shade of pale yellow, just yellow enough to stand out on a LCD screen, but not so bright as to show up when printed on a black and white printer.

 

[chapp333]

I do the same as Denial, with the addition of setting up a print area, header/footer and specifying a default black and white print in the page setup dialog ahead of time. I then typically set the file attributes to Read-Only so that the master copy does not get mutilated by accident.

 

[jghrist]

If the unlocked cells are always going to be the same cells, why not just change the background color of these cells. See

http://bellsouthpwp.net/j/i/jim_sandi/Radial Load Flow Feeder Input.PDF

for an example of a data input sheet where the blue background cells are unprotected.

 

[joerd]

ASAP Utilities allows you to conditionally select all unprotected cells. Then, apply formatting to the selection.

On a side note, you can define a style for the input cells so that they'll all look the same.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[CWEngineer]

The reason, I am trying to figuere this out is because I might have selected some cells that I did not want to unlock.

I guess I can go and select all the cells and lock them all again. Then Unlock the cells that I am intersted in and make sure only those are unlock.

Thanks

 

[joerd]

Run the following code in the immediate pane of the VB editor. (it is on a single line!)
It gives you a list of cell addresses that are unlocked. Modify as you like (you can for example shade all these cells)

for each c in activesheet.usedrange.cells : m$ = m$ & iif (c.locked, "" , c.address & chr(10) ) : next c : ? m$

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[TomBarsh]

Use the "Tab" key on a protected worksheet, each "tab" moves to the next unprotected cell.

(I color my unprotected "input" cells green for easy identification, then print in black and white.)

 

[CWEngineer]

Thanks for all your input. Using the "Tab" key and the curson moving to the next unprotected cell is what I was looking for. Now I know that other cells were not included as unprotected cells. For now this serves my purpose - I might look into the VB Code later on.

Thanks

 

[jartgo]

I'll second the vote for ASAP Utilities. I've just discovered it last week and it has a lot of neat little tricks just like this.

http://www.asap-utilities.com/