Prevent a selected range from viewing by others

[PatLee]

dear all,
pls advise any way to protect a selected range of cells, e.g. D210,
viewing by others or can be viewed by entering a password.

Thanks in advance
PL.

 

[Ms-Exl-Learner]

It cannot possible using the Protect Sheet method, since your query is
about protecting range of cells and its values from viewing. If it is
a whole row or column means we can. I don’t know whether it is
possible in VBA or not.

 

[Ms-Exl-Learner]

One way using the Protect Sheet Method:-

Select all cells by press Cntrl+A>>Do Right Click>>Format
Cells>>Protection>>Uncheck the Locked Check Box and give ok.

Now select D210>> Do Right Click>>Format Cells>>Protection>>Check
the Locked Check Box and give ok.

Press Alt+T+P+P to get the Protect Sheet Dialog Box. Under the “Allow
all users of this worksheet to:” UNCHECK the Select Locked Cells. In
“Password to Unprotect Sheet:” provide your desired password and give
ok. Again you need to type the same password for confirmation and
give Ok.

Now the user’s can able to access the cells which don’t have the lock
that is, here users can’t able to go or select D210 cells.

But the movement of the cursor around the worksheet using the keyboard
arrow keys is a BIG DRAWBACK.

Using the Protect Sheet method this is the only way to block users
from viewing or accessing your desired cells.

Changing the Font color to Background color and Cell Format Type to
Custom will also fail if the users have the access to those cells,
since the users can copy and paste the data to some other unlocked
cell of the same worksheet or other Worksheets and view it.

This is not the fair method to use, but I just want to let you know
this can be done using the above method.

 

[Gord Dibben]

I will not advise a method because there is no fool-proof method of doing this.

Any protection placed on the range of cells can easily be defeated.

You can follow ms-Exl-learner's suggestions to lock and format but a user can
simply reference those cells on another worksheet in same or another workbook.

If you don't want users to access data, do not include that data in your
worksheet or workbook.


Gord Dibben MS Excel MVP

 

[PatLee]

I will not advise a method because there is no fool-proof method of doingthis.

Any protection placed on the range of cells can easily be defeated.

You can follow ms-Exl-learner's suggestions to lock and format but a usercan
simply reference those cells on another worksheet in same or another workbook.

If you don't want users to access data, do not include that data in your
worksheet or workbook.

Gord Dibben     MS Excel MVP






- Show quoted text -

dear all,
thanks for yours advices.

Pat.

 

[CellShocked]

dear all,
thanks for yours advices.

Pat.

I use conditional formatting based on the value entered in a reference
cell.

The formatting for invisibility is white background, white lettering,
with white borders.

If one cannot get to the cells for data entry (view only), then it
should work as long as the reference cell has the value the validation
rule looks for.

As mentioned earlier, this does not keep someone from referencing the
cell range from within another worksheet or location within the current.

 

[Ms-Exl-Learner]

Gord Dibben Sir today I have learned another one more from your
post.

I hope this would be the right example for the Real Expert and
Learner(Me). Because I thought something in different angle by
missing the logic but your reply is awesome.

Few months back lots of experts are used to guide me whenever I do
mistake, but now it’s only few. I am glad that still you are staying
here (without moving to the New Forum of Microsoft) and it’s always
happy to receive your guidance.

 

[Gord Dibben]

Thanks for the feedback.

Gord

Gord Dibben Sir today I have learned another one more from your
post.

I hope this would be the right example for the Real Expert and
Learner(Me). Because I thought something in different angle by
missing the logic but your reply is awesome.

Few months back lots of experts are used to guide me whenever I do
mistake, but now it’s only few. I am glad that still you are staying
here (without moving to the New Forum of Microsoft) and it’s always
happy to receive your guidance.