×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Protected Sheets - Viewing Cells that have been Unlocked

Protected Sheets - Viewing Cells that have been Unlocked

Protected Sheets - Viewing Cells that have been Unlocked

(OP)
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.

RE: Protected Sheets - Viewing Cells that have been Unlocked

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.

RE: Protected Sheets - Viewing Cells that have been Unlocked

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.

RE: Protected Sheets - Viewing Cells that have been Unlocked

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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Protected Sheets - Viewing Cells that have been Unlocked

(OP)
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

RE: Protected Sheets - Viewing Cells that have been Unlocked

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)

CODE

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

Cheers,
Joerd

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

RE: Protected Sheets - Viewing Cells that have been Unlocked

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.)

RE: Protected Sheets - Viewing Cells that have been Unlocked

(OP)
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

  

RE: Protected Sheets - Viewing Cells that have been Unlocked

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/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources