 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2007Worksheet's protecting problem
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? That's the standard setting when you protect a worksheet. You don't have to do anything. Joerg > 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? > That's the standard setting when you protect a worksheet. You don't have to > do anything. [quoted text clipped - 9 lines] > > cells. > > could we turn it true without using vba? @ 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 On 2ÔÂ7ÈÕ, ÉÏÎç11ʱ08·Ö, "Joerg" <n...@email.address> wrote: > That's the standard setting when you protect a worksheet. You don't have to > do anything. [quoted text clipped - 9 lines] > > cells. > > could we turn it true without using vba? @ 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 [quoted text clipped - 47 lines] > 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 > > <fujing1...@gmail.com> wrote in message > [quoted text clipped - 5 lines] > > > cells. > > > could we turn it true without using vba? > <fujing1...@gmail.com> wrote in message > [quoted text clipped - 4 lines] > > do anything. > > Joerg > @ Joerg > Thanks very much for your answer. but I don't get the answer I want. [quoted text clipped - 13 lines] > Now I hope you got my problem. expect for your answer. > Best wishes! On 2$B7n(B8$BF|(B, $B2<8a(B5?36$BJ,(B, "Joerg" <n...@email.address> wrote: > 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 [quoted text clipped - 9 lines] > > 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 @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 > Thanks again for your kind-hearted help. [quoted text clipped - 3 lines] > Best wishes! > yours fujing Good luck! Joerg |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.