Worksheet's protecting problem

[fujing1003]

I want to achieve the following effect:
When some worksheet is protected. the user could only select the
locked cells. but he could do anything he want in the unlocked
cells.
could we turn it true without using vba?

 

[Joerg]

That's the standard setting when you protect a worksheet. You don't have to
do anything.
Joerg

 

[fujing1003]

That's the standard setting when you protect a worksheet. You don't have to
do anything.
Joerg

@ Joerg
Thanks very much for your answer. but I don't get the answer I want.
It's my fault in description of the problem.
Now I'll describe it in detail.
there are two categories of cells in some worksheet: locked cells and
unlocked cells.
when the worksheet above is protected, the user could not only change
value of the unlocked cells,but could also change their format. and he
could do nothing to the locked cells, except choosing them.
The above description is what I want to reach, but to my depress, when
the worksheet is protected. I couldn't format the unlocked cells.if
the format cells checkbox is not selected in "Protect Sheet" dialog
box. and in opposition to it, if the format cells checkbox is selected
in "Protect Sheet" dialog box,the locked cells could also be
formated.
Now I hope you got my problem. expect for your answer.
Best wishes!

 

[Joerg]

Sorry, my fault. I wasn't aware that unlocked cells cannot be formatted when
the sheet is locked. I can understand your frustration, but that seems to be
the way how Excel is designed.

The only solution I see (apart from using macros) is to uncheck "Select
locked cells" and to check "Format cells".
This would allow a user to format unlocked cells, but would prevent him from
selecting locked cells. Since he can't do anything with these locked cells,
that might even be a good solution.....

Good luck

Joerg

That's the standard setting when you protect a worksheet. You don't have to
do anything.
Joerg

@ Joerg
Thanks very much for your answer. but I don't get the answer I want.
It's my fault in description of the problem.
Now I'll describe it in detail.
there are two categories of cells in some worksheet: locked cells and
unlocked cells.
when the worksheet above is protected, the user could not only change
value of the unlocked cells,but could also change their format. and he
could do nothing to the locked cells, except choosing them.
The above description is what I want to reach, but to my depress, when
the worksheet is protected. I couldn't format the unlocked cells.if
the format cells checkbox is not selected in "Protect Sheet" dialog
box. and in opposition to it, if the format cells checkbox is selected
in "Protect Sheet" dialog box,the locked cells could also be
formated.
Now I hope you got my problem. expect for your answer.
Best wishes!

 

[fujing1003]

Sorry, my fault. I wasn't aware that unlocked cells cannot be formatted when
the sheet is locked. I can understand your frustration, but that seems tobe
the way how Excel is designed.

The only solution I see (apart from using macros) is to uncheck "Select
locked cells" and to check "Format cells".
This would allow a user to format unlocked cells, but would prevent him from
selecting locked cells. Since he can't do anything with these locked cells,
that might even be a good solution.....

Good luck

Joerg





@ Joerg
Thanks very much for your answer. but I don't get the answer I want.
It's my fault in description of the problem.
Now I'll describe it in detail.
there are two categories of cells in some worksheet: locked cells and
unlocked cells.
when the worksheet above is protected, the user could not only change
value of the unlocked cells,but could also change their format. and he
could do nothing to the locked cells, except choosing them.
The above description is what I want to reach, but to my depress, when
the worksheet is protected. I couldn't format the unlocked cells.if
the format cells checkbox is not selected in "Protect Sheet" dialog
box. and in opposition to it, if the format cells checkbox is selected
in "Protect Sheet" dialog box,the locked cells could also be
formated.
Now I hope you got my problem. expect for your answer.
Best wishes!

@ Joerg
Thank you very much for you rapid answer. the problem has puzzled me a
long time . the solution you gave above(to uncheck "Select> locked
cells" and to check "Format cells") has been thought by us already.
But we don't want to take it. we should allow user to select the
locked.
and as you know we could write code in worksheet_selectionchange
event to solve this problem,but it'll cost application's resource.
We want to find out a method that could achieve what we ask and won't
cost application's resource.
Thank you again for you kind-hearted help!
Best wishes!

Yours,fujing1003

 

[Joerg]

@ Joerg
Thanks very much for your answer. but I don't get the answer I want.
It's my fault in description of the problem.
Now I'll describe it in detail.
there are two categories of cells in some worksheet: locked cells and
unlocked cells.
when the worksheet above is protected, the user could not only change
value of the unlocked cells,but could also change their format. and he
could do nothing to the locked cells, except choosing them.
The above description is what I want to reach, but to my depress, when
the worksheet is protected. I couldn't format the unlocked cells.if
the format cells checkbox is not selected in "Protect Sheet" dialog
box. and in opposition to it, if the format cells checkbox is selected
in "Protect Sheet" dialog box,the locked cells could also be
formated.
Now I hope you got my problem. expect for your answer.
Best wishes!

Sorry, my fault. I wasn't aware that unlocked cells cannot be formatted when
the sheet is locked. I can understand your frustration, but that seems to be
the way how Excel is designed.

The only solution I see (apart from using macros) is to uncheck "Select
locked cells" and to check "Format cells".
This would allow a user to format unlocked cells, but would prevent him from
selecting locked cells. Since he can't do anything with these locked cells,
that might even be a good solution.....

Good luck

Joerg

@ Joerg
Thank you very much for you rapid answer. the problem has puzzled me a
long time . the solution you gave above(to uncheck "Select> locked
cells" and to check "Format cells") has been thought by us already.
But we don't want to take it. we should allow user to select the
locked.
and as you know we could write code in worksheet_selectionchange
event to solve this problem,but it'll cost application's resource.
We want to find out a method that could achieve what we ask and won't
cost application's resource.
Thank you again for you kind-hearted help!
Best wishes!

Yours,fujing1003


@fujing1003

Now your problem puzzles me as well...
I don't know what exactly you want your users to format. Normally password
protected sheets should require little formatting - if any - by the users,
so if the formatting options required are limited, you could try conditional
formatting (at least that works in password protected sheets).

Another clunky solution: You could provide preformatted "master cells",
which the user could copy to the target cells, thus applying the formats
needed. And this reveals a big and dangerous unconsistency of Excels
protection philosophy: While direct formatting is not possible, copying the
formats from other cells is. So even if a user might just want to copy a
value from another cell, he in fact copies the underlying formats,
conditional formats and data entry rules as well. Now THIS should be
prohibited, but isn't.

As for the code: If worksheet_selectionchange is too "heavy", because it
fires every time the selection changes, there are other possibilities. The
easiest I can think of: Allow formatting also for locked cells (what harm
can the users do if they can't change values?) and then use the
Workbook_BeforeSave event to reset the formats of the locked cells (you
could copy them from a hidden template sheet).

Good luck,

Joerg

 

[fujing1003]

@Joerg
Thanks again for your kind-hearted help.
Maybe I need to change my request. Because it nearly can't reach the
destination directly by excel's protection system. and It's hard to
find a balance between performance and efficiency by VBA.
Best wishes!
yours fujing

 

[Joerg]

@Joerg
Thanks again for your kind-hearted help.
Maybe I need to change my request. Because it nearly can't reach the
destination directly by excel's protection system. and It's hard to
find a balance between performance and efficiency by VBA.
Best wishes!
yours fujing

Good luck!
Joerg