If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
David Biddulph is correct
If you select ALL of the cells where you want numbers to go by other users, then select FormatCells and under Protection tab UNCHECK Locked - you are required to go to ToolsProtectionProtect Sheet ... and ... VOILA!!!
Users are able to enter information in default font but cannot change the size, color, or formatting of the cell... just verified it on MSFT Excel 2003 erik koepf wrote: How to lock cell format and structe in Excel 2003 08-Feb-09 I am not as technically sophisticated as most of the people replying to this post, but the problem i was having is exactly as you describe it, and all the proposed solutions seem waaaaaaaay too complicated. I found a simple solution for Excel 2003, so here it is. Sorry if this is obvious: If you have a sheet open and you want to lock the formatting (cell colors, borders, etc.) but want all the data to be able to be manipulated, go to Tools, Protection, Protect Workbook, then select the box that says Structure, and click Ok. All the other posts talk about Protecting Sheet, but that does not work. Protect Workbook is simple and easy. Previous Posts In This Thread: On Monday, February 25, 2008 4:51 PM GraceJean Jone wrote: Protect (Lock) Cell Formatting ONLY I understand how to protect a worksheet, and I know how to "unlock" certain cells in a worksheet, so that users of the worksheet can enter information into these unlocked cells. I have a worksheet where all I want the users to be able to do in certain cells is to be able to 'put in or insert' numbers. I don't want the users of this worksheet to be able to change the font, font color, font size, borders, etc. In other words, I want to create a worksheet where only numbers can be entered into certain cells, but no changes whatsoever can be made to the cell formatting of these cells. Thanks for your assistance. On Monday, February 25, 2008 5:14 PM David Biddulph wrote: I'm glad to hear that you know how to protect a sheet and how to lock and I am glad to hear that you know how to protect a sheet and how to lock and unlock cells. You therefore know how to answer your question. -- David Biddulph On Monday, February 25, 2008 5:40 PM GraceJeanJone wrote: Thanks for your reply, however, my question was not answered. Thanks for your reply, however, my question was not answered. I want to be able to create cells in a worksheet where users can input numbers, but cannot change any of the formatting of the cell, such as font style, font size, font color, borders, etc. Thanks again. "David Biddulph" wrote: On Monday, February 25, 2008 8:17 PM Cimjet wrote: Hi GraceJeanI don't know what version of excel you are using but if you Hi GraceJean I do not know what version of excel you are using but if you protect sheet, you cannot format,change fonts or resize etc... i am using excel3. Regards Cimjet On Tuesday, February 26, 2008 3:24 AM David Biddulph wrote: Which version of Excel are you using? Which version of Excel are you using? The cells that you want to change the values you have unlocked with Format/ Cells/ Unlock? You have gone to Tools/ Protection/ Protect Sheet? The default at that stage (at least in my Excel 2003) is to allow users to select cells, but not to allow them to format cells, so that default will do what you want. Did you change any of the options at that stage? What is your problem having protected the sheet? Is it allowing users to change format although when you protected the sheet you have not selected the option to allow formatting? Is it not allowing users to select cells which you have unlocked? -- David Biddulph "GraceJean Jones" wrote in message ... On Friday, February 29, 2008 1:00 AM GraceJean Jone wrote: Thanks again for your response. Thanks again for your response. In the worksheet I have unlocked certain cells using like you said: Format/ Cells/ Unlock. Then, like you said, I go to Tools/ Protection/ Protect Sheet. If you allow users to "select cells," this option means the users of the worksheet can put their cursor on "locked" cells & select them. This DOES NOT mean that users, once selecting a "locked" cell can do anything with the cell at all. If you do not allow users to "select cells," this then means that the user of the worksheet cannot even get their cursor to move onto a cell that has not been unlocked. If the user trys clicking on a "locked cell" or using the arrows to move around, the cursor will just "jump" over cells that have been "locked" (if the "select cells" if left unchecked). So, in other words, by checking "select cells" all this does is it allows users to get their cursor moved onto or over "locked" cells. If I unlock a cell, users have access to this cell, to input numbers. But the users unfortunately also have full access to change the color, to change the font, to change the borders of the cell, etc. I still cannot figure out a way to unlock a cell so that the only thing users can do in this cell is input a number, & nothing else. Thanks again for your feedback, if you know of something else I can try, that would be great. "David Biddulph" wrote: On Friday, February 29, 2008 2:54 AM David Biddulph wrote: Protect (Lock) Cell Formatting ONLY You failed to answer my first question: "Which version of Excel are you using?" In Excel 2003, and in any other version which I have used, the method which I described does what you ask. I allow users to select cells, and that allows them to change the content of, but not the format of, the unlocked cells (but doesn't allow them to change unlocked cells). In whichever version you have, are you saying that if you allow users to select locked and unlocked cells (the default settings), then the unlocked cells behave exactly the same as the locked cells? [Perhaps you'd better check again that you have actually unlocked the cells that you think you've unlocked?] I would be fascinated to hear if anyone else suffers from these same symptoms. Have a look at Excel help. The topic "About worksheet and workbook protection" and the sub-topic "Protecting worksheet elements" address the relevant area. If your Excel isn't behaving the way that it should do, you may need to reinstall, but I would suggest checking again carefully that you've got the settings correct on your cells and on your worksheet before you resort to that drastic step. -- David Biddulph "GraceJean Jones" GraceJean wrote in message ... On Friday, February 29, 2008 1:07 PM GraceJeanJone wrote: Thanks again for your reply. Thanks again for your reply. Your time in trying to help is very much appreciated. First of all, I was using Excel 2003 at somebody else's computer. I do not have Excel 2003 on my computer to test this all out. However, I went to the MS website that you suggested. I have included a few comments that I think might explain what is going on: * By default the Select locked cells check box is selected. This check box enables users to select cells with the Locked check box selected in the Format Cells dialog box. When the Select locked cells check box is selected, the Select unlocked cells check box is automatically selected. * By default the Select unlocked cells check box is selected. This check box enables users to select cells with the Locked check box cleared in the Format Cells dialog box. When the Select unlocked cells check box is cleared, the Select locked cells check box is automatically cleared. If there are no unlocked cells on a protected sheet and this check box is not selected, users cannot select any cells on the worksheet. * Note: You cannot permit formatting of unlocked cells only. Maybe you can test out the issue I am having (if you have a moment). Unfortunately, this makes me kind of mad, because I'm not at a computer right now that has Excel 2003 running, if I did, I would do this myself. However, when I was at my friend's computer earlier this week, I'm pretty sure I already did what I'm asking below. And...it did not work the way I wanted it to. i.e., my friend wanted me to lock all but certain cells on a worksheet that she was going to give to people to input numbers. My friend was not happy, though, that the users of the worksheet could input numbers just fine into unlocked cells, BUT...they also were able to change the formatting of the cell (color, font, font size, borders, etc.), which she did not want them to be able to do. We tried & tried & tried, we could not figure out a way to do what she wanted. Anyway, if you can try the following, you will see what I am talking about. -Open Excel 2003 -Put your cursor in cell C3 & Highlight the block of cells C3 thru E5. -Select Format/ Cells/ Unlock -Select Tools/ Protection/ Protect Sheet -Make sure the "Select locked cells" box is NOT checked You will then see that all this does it that it does not allow you as the user to get your cursor into any cells other than those that you previously unlocked (i.e., the range of C3 thru E5) If your cursor is in cell E5 & you hit the down arrow, your cursor will not move to cell E6, it will move to cell C3. i.e., the user is not able to select any locked cells whatsoever anywhere on the worksheet. The user will only be able to select cells that have been previously unlocked. If you are able to tell me the further steps at this point that I need to go thru to allow users of this worksheet to be able to input numbers in to the range of cells of C3 thru E5, but NOT be able to change any of the formatting of these cells, please just pass along the additional steps that I need to take to be able to do this. I can call my friend and walk her thru the steps over the phone. Again, your time in helping me out is greatly appreciated. If I can figure this out, this will really be great, and will help us out a lot. Thanks again. "David Biddulph" wrote: On Friday, February 29, 2008 1:46 PM David Biddulph wrote: No more steps needed. No more steps needed. As I said before, in that situation you can put numbers in the unlocked cells but cannot change the formatting. -- David Biddulph On Thursday, June 26, 2008 2:19 PM Sam Hills wrote: Protecting cell formatting I am having a similar problem. I can protect cells so that the user cannot select them, and unprotect the data-entry cells so that the user can enter data. "Format cells" is unchecked, so the "Format cells" option in the right-click menu is greyed out, and the "Cells" option on the "Format" menu is likewise greyed out. However, if the user copies one data-entry cell and pastes it into another, the formatting is pasted too. To demonstrate this: 1. Format column A as text, column B as date and column C as number. On the Protection tab, uncheck "locked". 2. Protect the sheet. Make sure "Format Cells" is unchecked. 3. Select a cell in column A and copy it to the clipboard with Ctrl-C. 4. Paste that cell into cells B1 and C1. 5. Turn sheet protection off. 6. Right-click on cell B1 and select "Format cells". The format has been changed to Text. Likewise for C1. How can I protect cells so that pasting another cell will only paste the source cell's value but not its formatting? On Tuesday, January 27, 2009 4:47 PM abc def wrote: protect cells so that pasting another cell will only paste the source cell's value but not its formatting Hello Mr. Hills, did you ever find a solution to "pasting another cell will only paste the source cell's value but not its formatting" I too am looking for a solution. Thanks! On Sunday, February 08, 2009 3:48 PM erik koepf wrote: How to lock cell format and structe in Excel 2003 I am not as technically sophisticated as most of the people replying to this post, but the problem i was having is exactly as you describe it, and all the proposed solutions seem waaaaaaaay too complicated. I found a simple solution for Excel 2003, so here it is. Sorry if this is obvious: If you have a sheet open and you want to lock the formatting (cell colors, borders, etc.) but want all the data to be able to be manipulated, go to Tools, Protection, Protect Workbook, then select the box that says Structure, and click Ok. All the other posts talk about Protecting Sheet, but that does not work. Protect Workbook is simple and easy. EggHeadCafe - Software Developer Portal of Choice ASP.NET AJAX Maintain Scroll Position from a Partial Page Update http://www.eggheadcafe.com/tutorials...ntain-scr.aspx |
#2
|
|||
|
|||
Problem Still exist
Hello,
My problem is similar / same.. I want to create a worksheet where certain cells are NOT editable AT ALL and other cells where users are able to enter values but NOT change the cell formatting inadvertently.... To replicate my problem :- 1) Create New Workbook 2) cell A1 is the editable cell (all other cells must NOT be editable) 3) cell A1 will be a currency value - set format of cell to accountancy with currency symbol 4) By default all cells are 'locked' on Excel sheets .. cell A1 will need to be 'unlocked' to allow users to enter values once the sheet is protected. therefore, Goto format cells, protection tab, untick 'locked' 5) protect sheet 6) try typing something into cell A2 - due to protection, you are unable to edit - this is ok - that's what i want 5) enter 1:00 into cell A1 This has now changed the cell format for cell A1 to TIME. typing in just the number 1 (for one pound) into cell A1 now, will cause 00:00 to be displayed How can I protect cell A1 so that values can be entered, but cell formatting should never change ? Sascha wrote: David Biddulph is correct 18-Nov-09 If you select ALL of the cells where you want numbers to go by other users, then select FormatCells and under Protection tab UNCHECK Locked - you are required to go to ToolsProtectionProtect Sheet ... and ... VOILA!!! Users are able to enter information in default font but cannot change the size, color, or formatting of the cell... just verified it on MSFT Excel 2003 Previous Posts In This Thread: On Monday, February 25, 2008 4:51 PM GraceJean Jone wrote: Protect (Lock) Cell Formatting ONLY I understand how to protect a worksheet, and I know how to "unlock" certain cells in a worksheet, so that users of the worksheet can enter information into these unlocked cells. I have a worksheet where all I want the users to be able to do in certain cells is to be able to 'put in or insert' numbers. I don't want the users of this worksheet to be able to change the font, font color, font size, borders, etc. In other words, I want to create a worksheet where only numbers can be entered into certain cells, but no changes whatsoever can be made to the cell formatting of these cells. Thanks for your assistance. On Monday, February 25, 2008 5:14 PM David Biddulph wrote: I'm glad to hear that you know how to protect a sheet and how to lock and I am glad to hear that you know how to protect a sheet and how to lock and unlock cells. You therefore know how to answer your question. -- David Biddulph On Monday, February 25, 2008 5:40 PM GraceJeanJone wrote: Thanks for your reply, however, my question was not answered. Thanks for your reply, however, my question was not answered. I want to be able to create cells in a worksheet where users can input numbers, but cannot change any of the formatting of the cell, such as font style, font size, font color, borders, etc. Thanks again. "David Biddulph" wrote: On Monday, February 25, 2008 8:17 PM Cimjet wrote: Hi GraceJeanI don't know what version of excel you are using but if you Hi GraceJean I do not know what version of excel you are using but if you protect sheet, you cannot format,change fonts or resize etc... i am using excel3. Regards Cimjet On Tuesday, February 26, 2008 3:24 AM David Biddulph wrote: Which version of Excel are you using? Which version of Excel are you using? The cells that you want to change the values you have unlocked with Format/ Cells/ Unlock? You have gone to Tools/ Protection/ Protect Sheet? The default at that stage (at least in my Excel 2003) is to allow users to select cells, but not to allow them to format cells, so that default will do what you want. Did you change any of the options at that stage? What is your problem having protected the sheet? Is it allowing users to change format although when you protected the sheet you have not selected the option to allow formatting? Is it not allowing users to select cells which you have unlocked? -- David Biddulph "GraceJean Jones" wrote in message ... On Friday, February 29, 2008 1:00 AM GraceJean Jone wrote: Thanks again for your response. Thanks again for your response. In the worksheet I have unlocked certain cells using like you said: Format/ Cells/ Unlock. Then, like you said, I go to Tools/ Protection/ Protect Sheet. If you allow users to "select cells," this option means the users of the worksheet can put their cursor on "locked" cells & select them. This DOES NOT mean that users, once selecting a "locked" cell can do anything with the cell at all. If you do not allow users to "select cells," this then means that the user of the worksheet cannot even get their cursor to move onto a cell that has not been unlocked. If the user trys clicking on a "locked cell" or using the arrows to move around, the cursor will just "jump" over cells that have been "locked" (if the "select cells" if left unchecked). So, in other words, by checking "select cells" all this does is it allows users to get their cursor moved onto or over "locked" cells. If I unlock a cell, users have access to this cell, to input numbers. But the users unfortunately also have full access to change the color, to change the font, to change the borders of the cell, etc. I still cannot figure out a way to unlock a cell so that the only thing users can do in this cell is input a number, & nothing else. Thanks again for your feedback, if you know of something else I can try, that would be great. "David Biddulph" wrote: On Friday, February 29, 2008 2:54 AM David Biddulph wrote: Protect (Lock) Cell Formatting ONLY You failed to answer my first question: "Which version of Excel are you using?" In Excel 2003, and in any other version which I have used, the method which I described does what you ask. I allow users to select cells, and that allows them to change the content of, but not the format of, the unlocked cells (but doesn't allow them to change unlocked cells). In whichever version you have, are you saying that if you allow users to select locked and unlocked cells (the default settings), then the unlocked cells behave exactly the same as the locked cells? [Perhaps you'd better check again that you have actually unlocked the cells that you think you've unlocked?] I would be fascinated to hear if anyone else suffers from these same symptoms. Have a look at Excel help. The topic "About worksheet and workbook protection" and the sub-topic "Protecting worksheet elements" address the relevant area. If your Excel isn't behaving the way that it should do, you may need to reinstall, but I would suggest checking again carefully that you've got the settings correct on your cells and on your worksheet before you resort to that drastic step. -- David Biddulph "GraceJean Jones" GraceJean wrote in message ... On Friday, February 29, 2008 1:07 PM GraceJeanJone wrote: Thanks again for your reply. Thanks again for your reply. Your time in trying to help is very much appreciated. First of all, I was using Excel 2003 at somebody else's computer. I do not have Excel 2003 on my computer to test this all out. However, I went to the MS website that you suggested. I have included a few comments that I think might explain what is going on: * By default the Select locked cells check box is selected. This check box enables users to select cells with the Locked check box selected in the Format Cells dialog box. When the Select locked cells check box is selected, the Select unlocked cells check box is automatically selected. * By default the Select unlocked cells check box is selected. This check box enables users to select cells with the Locked check box cleared in the Format Cells dialog box. When the Select unlocked cells check box is cleared, the Select locked cells check box is automatically cleared. If there are no unlocked cells on a protected sheet and this check box is not selected, users cannot select any cells on the worksheet. * Note: You cannot permit formatting of unlocked cells only. Maybe you can test out the issue I am having (if you have a moment). Unfortunately, this makes me kind of mad, because I'm not at a computer right now that has Excel 2003 running, if I did, I would do this myself. However, when I was at my friend's computer earlier this week, I'm pretty sure I already did what I'm asking below. And...it did not work the way I wanted it to. i.e., my friend wanted me to lock all but certain cells on a worksheet that she was going to give to people to input numbers. My friend was not happy, though, that the users of the worksheet could input numbers just fine into unlocked cells, BUT...they also were able to change the formatting of the cell (color, font, font size, borders, etc.), which she did not want them to be able to do. We tried & tried & tried, we could not figure out a way to do what she wanted. Anyway, if you can try the following, you will see what I am talking about. -Open Excel 2003 -Put your cursor in cell C3 & Highlight the block of cells C3 thru E5. -Select Format/ Cells/ Unlock -Select Tools/ Protection/ Protect Sheet -Make sure the "Select locked cells" box is NOT checked You will then see that all this does it that it does not allow you as the user to get your cursor into any cells other than those that you previously unlocked (i.e., the range of C3 thru E5) If your cursor is in cell E5 & you hit the down arrow, your cursor will not move to cell E6, it will move to cell C3. i.e., the user is not able to select any locked cells whatsoever anywhere on the worksheet. The user will only be able to select cells that have been previously unlocked. If you are able to tell me the further steps at this point that I need to go thru to allow users of this worksheet to be able to input numbers in to the range of cells of C3 thru E5, but NOT be able to change any of the formatting of these cells, please just pass along the additional steps that I need to take to be able to do this. I can call my friend and walk her thru the steps over the phone. Again, your time in helping me out is greatly appreciated. If I can figure this out, this will really be great, and will help us out a lot. Thanks again. "David Biddulph" wrote: On Friday, February 29, 2008 1:46 PM David Biddulph wrote: No more steps needed. No more steps needed. As I said before, in that situation you can put numbers in the unlocked cells but cannot change the formatting. -- David Biddulph On Thursday, June 26, 2008 2:19 PM Sam Hills wrote: Protecting cell formatting I am having a similar problem. I can protect cells so that the user cannot select them, and unprotect the data-entry cells so that the user can enter data. "Format cells" is unchecked, so the "Format cells" option in the right-click menu is greyed out, and the "Cells" option on the "Format" menu is likewise greyed out. However, if the user copies one data-entry cell and pastes it into another, the formatting is pasted too. To demonstrate this: 1. Format column A as text, column B as date and column C as number. On the Protection tab, uncheck "locked". 2. Protect the sheet. Make sure "Format Cells" is unchecked. 3. Select a cell in column A and copy it to the clipboard with Ctrl-C. 4. Paste that cell into cells B1 and C1. 5. Turn sheet protection off. 6. Right-click on cell B1 and select "Format cells". The format has been changed to Text. Likewise for C1. How can I protect cells so that pasting another cell will only paste the source cell's value but not its formatting? On Tuesday, January 27, 2009 4:47 PM abc def wrote: protect cells so that pasting another cell will only paste the source cell's value but not its formatting Hello Mr. Hills, did you ever find a solution to "pasting another cell will only paste the source cell's value but not its formatting" I too am looking for a solution. Thanks! On Sunday, February 08, 2009 3:48 PM erik koepf wrote: How to lock cell format and structe in Excel 2003 I am not as technically sophisticated as most of the people replying to this post, but the problem i was having is exactly as you describe it, and all the proposed solutions seem waaaaaaaay too complicated. I found a simple solution for Excel 2003, so here it is. Sorry if this is obvious: If you have a sheet open and you want to lock the formatting (cell colors, borders, etc.) but want all the data to be able to be manipulated, go to Tools, Protection, Protect Workbook, then select the box that says Structure, and click Ok. All the other posts talk about Protecting Sheet, but that does not work. Protect Workbook is simple and easy. On Wednesday, November 18, 2009 5:17 PM Sascha wrote: David Biddulph is correct If you select ALL of the cells where you want numbers to go by other users, then select FormatCells and under Protection tab UNCHECK Locked - you are required to go to ToolsProtectionProtect Sheet ... and ... VOILA!!! Users are able to enter information in default font but cannot change the size, color, or formatting of the cell... just verified it on MSFT Excel 2003 Submitted via EggHeadCafe - Software Developer Portal of Choice Generate Machine Keys for ASP.NET Web Farms http://www.eggheadcafe.com/tutorials...-keys-for.aspx |
#4
|
|||
|
|||
The possible solution
Check the following:
When you protect the workbook (Standard way): Tools/Protection/Protect worksheet/, you should have the whole list of options under: "Allow all users of this worksheet to:" -select locked cells -select unlocked cells - format cells -etc... make sure that "format cells" (as well as other "format" options) are unchecked. I'm surprised that you have this problem, since the default is two first options are checked, and the rest is unchecked. Ebrahim Makda wrote: Problem Still exist 01-Jan-10 Hello, My problem is similar / same.. I want to create a worksheet where certain cells are NOT editable AT ALL and other cells where users are able to enter values but NOT change the cell formatting inadvertently.... To replicate my problem :- 1) Create New Workbook 2) cell A1 is the editable cell (all other cells must NOT be editable) 3) cell A1 will be a currency value - set format of cell to accountancy with currency symbol 4) By default all cells are 'locked' on Excel sheets .. cell A1 will need to be 'unlocked' to allow users to enter values once the sheet is protected. therefore, Goto format cells, protection tab, untick 'locked' 5) protect sheet 6) try typing something into cell A2 - due to protection, you are unable to edit - this is ok - that's what i want 5) enter 1:00 into cell A1 This has now changed the cell format for cell A1 to TIME. typing in just the number 1 (for one pound) into cell A1 now, will cause 00:00 to be displayed How can I protect cell A1 so that values can be entered, but cell formatting should never change ? Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice C# Email With Attachment / Upload / Validation http://www.eggheadcafe.com/tutorials...ttachment.aspx |
#5
|
|||
|
|||
Further Issues
I have similar Issue however to add a posible vital point when locked down formatting can not be altered in the normal way. but can be changed by cut and pasting already formated data into the locked sheet. i.e. if my sheet is formatted to allow the entry of a number into a cell and some numpty cuts and pastes a time entry into that cell the formatting of that locked cell is changed to time. similar happens when data dragging the format will be dragged to regardless of locking and protecting.
To fix I think i need some way of forcing cut and paste to default to paste special / Values which prevents the format changing.. Lena Yampolsky wrote: The possible solution 14-Jan-10 Check the following: When you protect the workbook (Standard way): Tools/Protection/Protect worksheet/, you should have the whole list of options under: "Allow all users of this worksheet to:" -select locked cells -select unlocked cells - format cells -etc... make sure that "format cells" (as well as other "format" options) are unchecked. I'm surprised that you have this problem, since the default is two first options are checked, and the rest is unchecked. Previous Posts In This Thread: On Monday, February 25, 2008 4:51 PM GraceJean Jone wrote: Protect (Lock) Cell Formatting ONLY I understand how to protect a worksheet, and I know how to "unlock" certain cells in a worksheet, so that users of the worksheet can enter information into these unlocked cells. I have a worksheet where all I want the users to be able to do in certain cells is to be able to 'put in or insert' numbers. I don't want the users of this worksheet to be able to change the font, font color, font size, borders, etc. In other words, I want to create a worksheet where only numbers can be entered into certain cells, but no changes whatsoever can be made to the cell formatting of these cells. Thanks for your assistance. On Monday, February 25, 2008 5:14 PM David Biddulph wrote: I'm glad to hear that you know how to protect a sheet and how to lock and I am glad to hear that you know how to protect a sheet and how to lock and unlock cells. You therefore know how to answer your question. -- David Biddulph On Monday, February 25, 2008 5:40 PM GraceJeanJone wrote: Thanks for your reply, however, my question was not answered. Thanks for your reply, however, my question was not answered. I want to be able to create cells in a worksheet where users can input numbers, but cannot change any of the formatting of the cell, such as font style, font size, font color, borders, etc. Thanks again. "David Biddulph" wrote: On Monday, February 25, 2008 8:17 PM Cimjet wrote: Hi GraceJeanI don't know what version of excel you are using but if you Hi GraceJean I do not know what version of excel you are using but if you protect sheet, you cannot format,change fonts or resize etc... i am using excel3. Regards Cimjet On Tuesday, February 26, 2008 3:24 AM David Biddulph wrote: Which version of Excel are you using? Which version of Excel are you using? The cells that you want to change the values you have unlocked with Format/ Cells/ Unlock? You have gone to Tools/ Protection/ Protect Sheet? The default at that stage (at least in my Excel 2003) is to allow users to select cells, but not to allow them to format cells, so that default will do what you want. Did you change any of the options at that stage? What is your problem having protected the sheet? Is it allowing users to change format although when you protected the sheet you have not selected the option to allow formatting? Is it not allowing users to select cells which you have unlocked? -- David Biddulph "GraceJean Jones" wrote in message ... On Friday, February 29, 2008 1:00 AM GraceJean Jone wrote: Thanks again for your response. Thanks again for your response. In the worksheet I have unlocked certain cells using like you said: Format/ Cells/ Unlock. Then, like you said, I go to Tools/ Protection/ Protect Sheet. If you allow users to "select cells," this option means the users of the worksheet can put their cursor on "locked" cells & select them. This DOES NOT mean that users, once selecting a "locked" cell can do anything with the cell at all. If you do not allow users to "select cells," this then means that the user of the worksheet cannot even get their cursor to move onto a cell that has not been unlocked. If the user trys clicking on a "locked cell" or using the arrows to move around, the cursor will just "jump" over cells that have been "locked" (if the "select cells" if left unchecked). So, in other words, by checking "select cells" all this does is it allows users to get their cursor moved onto or over "locked" cells. If I unlock a cell, users have access to this cell, to input numbers. But the users unfortunately also have full access to change the color, to change the font, to change the borders of the cell, etc. I still cannot figure out a way to unlock a cell so that the only thing users can do in this cell is input a number, & nothing else. Thanks again for your feedback, if you know of something else I can try, that would be great. "David Biddulph" wrote: On Friday, February 29, 2008 2:54 AM David Biddulph wrote: Protect (Lock) Cell Formatting ONLY You failed to answer my first question: "Which version of Excel are you using?" In Excel 2003, and in any other version which I have used, the method which I described does what you ask. I allow users to select cells, and that allows them to change the content of, but not the format of, the unlocked cells (but doesn't allow them to change unlocked cells). In whichever version you have, are you saying that if you allow users to select locked and unlocked cells (the default settings), then the unlocked cells behave exactly the same as the locked cells? [Perhaps you'd better check again that you have actually unlocked the cells that you think you've unlocked?] I would be fascinated to hear if anyone else suffers from these same symptoms. Have a look at Excel help. The topic "About worksheet and workbook protection" and the sub-topic "Protecting worksheet elements" address the relevant area. If your Excel isn't behaving the way that it should do, you may need to reinstall, but I would suggest checking again carefully that you've got the settings correct on your cells and on your worksheet before you resort to that drastic step. -- David Biddulph "GraceJean Jones" GraceJean wrote in message ... On Friday, February 29, 2008 1:07 PM GraceJeanJone wrote: Thanks again for your reply. Thanks again for your reply. Your time in trying to help is very much appreciated. First of all, I was using Excel 2003 at somebody else's computer. I do not have Excel 2003 on my computer to test this all out. However, I went to the MS website that you suggested. I have included a few comments that I think might explain what is going on: * By default the Select locked cells check box is selected. This check box enables users to select cells with the Locked check box selected in the Format Cells dialog box. When the Select locked cells check box is selected, the Select unlocked cells check box is automatically selected. * By default the Select unlocked cells check box is selected. This check box enables users to select cells with the Locked check box cleared in the Format Cells dialog box. When the Select unlocked cells check box is cleared, the Select locked cells check box is automatically cleared. If there are no unlocked cells on a protected sheet and this check box is not selected, users cannot select any cells on the worksheet. * Note: You cannot permit formatting of unlocked cells only. Maybe you can test out the issue I am having (if you have a moment). Unfortunately, this makes me kind of mad, because I'm not at a computer right now that has Excel 2003 running, if I did, I would do this myself. However, when I was at my friend's computer earlier this week, I'm pretty sure I already did what I'm asking below. And...it did not work the way I wanted it to. i.e., my friend wanted me to lock all but certain cells on a worksheet that she was going to give to people to input numbers. My friend was not happy, though, that the users of the worksheet could input numbers just fine into unlocked cells, BUT...they also were able to change the formatting of the cell (color, font, font size, borders, etc.), which she did not want them to be able to do. We tried & tried & tried, we could not figure out a way to do what she wanted. Anyway, if you can try the following, you will see what I am talking about. -Open Excel 2003 -Put your cursor in cell C3 & Highlight the block of cells C3 thru E5. -Select Format/ Cells/ Unlock -Select Tools/ Protection/ Protect Sheet -Make sure the "Select locked cells" box is NOT checked You will then see that all this does it that it does not allow you as the user to get your cursor into any cells other than those that you previously unlocked (i.e., the range of C3 thru E5) If your cursor is in cell E5 & you hit the down arrow, your cursor will not move to cell E6, it will move to cell C3. i.e., the user is not able to select any locked cells whatsoever anywhere on the worksheet. The user will only be able to select cells that have been previously unlocked. If you are able to tell me the further steps at this point that I need to go thru to allow users of this worksheet to be able to input numbers in to the range of cells of C3 thru E5, but NOT be able to change any of the formatting of these cells, please just pass along the additional steps that I need to take to be able to do this. I can call my friend and walk her thru the steps over the phone. Again, your time in helping me out is greatly appreciated. If I can figure this out, this will really be great, and will help us out a lot. Thanks again. "David Biddulph" wrote: On Friday, February 29, 2008 1:46 PM David Biddulph wrote: No more steps needed. No more steps needed. As I said before, in that situation you can put numbers in the unlocked cells but cannot change the formatting. -- David Biddulph On Thursday, June 26, 2008 2:19 PM Sam Hills wrote: Protecting cell formatting I am having a similar problem. I can protect cells so that the user cannot select them, and unprotect the data-entry cells so that the user can enter data. "Format cells" is unchecked, so the "Format cells" option in the right-click menu is greyed out, and the "Cells" option on the "Format" menu is likewise greyed out. However, if the user copies one data-entry cell and pastes it into another, the formatting is pasted too. To demonstrate this: 1. Format column A as text, column B as date and column C as number. On the Protection tab, uncheck "locked". 2. Protect the sheet. Make sure "Format Cells" is unchecked. 3. Select a cell in column A and copy it to the clipboard with Ctrl-C. 4. Paste that cell into cells B1 and C1. 5. Turn sheet protection off. 6. Right-click on cell B1 and select "Format cells". The format has been changed to Text. Likewise for C1. How can I protect cells so that pasting another cell will only paste the source cell's value but not its formatting? On Tuesday, January 27, 2009 4:47 PM abc def wrote: protect cells so that pasting another cell will only paste the source cell's value but not its formatting Hello Mr. Hills, did you ever find a solution to "pasting another cell will only paste the source cell's value but not its formatting" I too am looking for a solution. Thanks! On Sunday, February 08, 2009 3:48 PM erik koepf wrote: How to lock cell format and structe in Excel 2003 I am not as technically sophisticated as most of the people replying to this post, but the problem i was having is exactly as you describe it, and all the proposed solutions seem waaaaaaaay too complicated. I found a simple solution for Excel 2003, so here it is. Sorry if this is obvious: If you have a sheet open and you want to lock the formatting (cell colors, borders, etc.) but want all the data to be able to be manipulated, go to Tools, Protection, Protect Workbook, then select the box that says Structure, and click Ok. All the other posts talk about Protecting Sheet, but that does not work. Protect Workbook is simple and easy. On Wednesday, November 18, 2009 5:17 PM Sascha wrote: David Biddulph is correct If you select ALL of the cells where you want numbers to go by other users, then select FormatCells and under Protection tab UNCHECK Locked - you are required to go to ToolsProtectionProtect Sheet ... and ... VOILA!!! Users are able to enter information in default font but cannot change the size, color, or formatting of the cell... just verified it on MSFT Excel 2003 On Friday, January 01, 2010 7:10 PM Ebrahim Makda wrote: Problem Still exist Hello, My problem is similar / same.. I want to create a worksheet where certain cells are NOT editable AT ALL and other cells where users are able to enter values but NOT change the cell formatting inadvertently.... To replicate my problem :- 1) Create New Workbook 2) cell A1 is the editable cell (all other cells must NOT be editable) 3) cell A1 will be a currency value - set format of cell to accountancy with currency symbol 4) By default all cells are 'locked' on Excel sheets .. cell A1 will need to be 'unlocked' to allow users to enter values once the sheet is protected. therefore, Goto format cells, protection tab, untick 'locked' 5) protect sheet 6) try typing something into cell A2 - due to protection, you are unable to edit - this is ok - that's what i want 5) enter 1:00 into cell A1 This has now changed the cell format for cell A1 to TIME. typing in just the number 1 (for one pound) into cell A1 now, will cause 00:00 to be displayed How can I protect cell A1 so that values can be entered, but cell formatting should never change ? On Friday, January 01, 2010 7:51 PM Ebrahim Makda wrote: Problem Still exist Hello, My problem is similar / same.. I want to create a worksheet where certain cells are NOT editable AT ALL and other cells where users are able to enter values but NOT change the cell formatting inadvertently.... To replicate my problem :- 1) Create New Workbook 2) cell A1 is the editable cell (all other cells must NOT be editable) 3) cell A1 will be a currency value - set format of cell to accountancy with currency symbol 4) By default all cells are 'locked' on Excel sheets .. cell A1 will need to be 'unlocked' to allow users to enter values once the sheet is protected. therefore, Goto format cells, protection tab, untick 'locked' 5) protect sheet 6) try typing something into cell A2 - due to protection, you are unable to edit - this is ok - that's what i want 5) enter 1:00 into cell A1 This has now changed the cell format for cell A1 to TIME. typing in just the number 1 (for one pound) into cell A1 now, will cause 00:00 to be displayed How can I protect cell A1 so that values can be entered, but cell formatting should never change ? On Thursday, January 14, 2010 10:53 AM Lena Yampolsky wrote: The possible solution Check the following: When you protect the workbook (Standard way): Tools/Protection/Protect worksheet/, you should have the whole list of options under: "Allow all users of this worksheet to:" -select locked cells -select unlocked cells - format cells -etc... make sure that "format cells" (as well as other "format" options) are unchecked. I'm surprised that you have this problem, since the default is two first options are checked, and the rest is unchecked. Submitted via EggHeadCafe - Software Developer Portal of Choice How to Annotate Images from a database in a web page http://www.eggheadcafe.com/tutorials...images-fr.aspx |
#6
|
|||
|
|||
Allow Users to Edit Ranges
I realise this is an old thread but the solution is still worth putting out there ... try the following:
- Lock all the cells that you don't want edited (whether it be formula or formatting) Format Cells/Protection - Go to Tools menu, under 'Protection' select the menu item below 'Protect Sheet' which should be 'Allow Users to Edit Ranges' - In the dialog box select 'New', select the range(s) that you would like to allow the users to change the values of - Protect your sheet That should solve your problems HTH Emma Clive Sinclair wrote: Further Issues 12-Mar-10 I have similar Issue however to add a posible vital point when locked down formatting can not be altered in the normal way. but can be changed by cut and pasting already formated data into the locked sheet. i.e. if my sheet is formatted to allow the entry of a number into a cell and some numpty cuts and pastes a time entry into that cell the formatting of that locked cell is changed to time. similar happens when data dragging the format will be dragged to regardless of locking and protecting. To fix I think i need some way of forcing cut and paste to default to paste special / Values which prevents the format changing.. Previous Posts In This Thread: On Monday, February 25, 2008 4:51 PM GraceJean Jone wrote: Protect (Lock) Cell Formatting ONLY I understand how to protect a worksheet, and I know how to "unlock" certain cells in a worksheet, so that users of the worksheet can enter information into these unlocked cells. I have a worksheet where all I want the users to be able to do in certain cells is to be able to 'put in or insert' numbers. I don't want the users of this worksheet to be able to change the font, font color, font size, borders, etc. In other words, I want to create a worksheet where only numbers can be entered into certain cells, but no changes whatsoever can be made to the cell formatting of these cells. Thanks for your assistance. On Monday, February 25, 2008 5:14 PM David Biddulph wrote: I'm glad to hear that you know how to protect a sheet and how to lock and I am glad to hear that you know how to protect a sheet and how to lock and unlock cells. You therefore know how to answer your question. -- David Biddulph On Monday, February 25, 2008 5:40 PM GraceJeanJone wrote: Thanks for your reply, however, my question was not answered. Thanks for your reply, however, my question was not answered. I want to be able to create cells in a worksheet where users can input numbers, but cannot change any of the formatting of the cell, such as font style, font size, font color, borders, etc. Thanks again. "David Biddulph" wrote: On Monday, February 25, 2008 8:17 PM Cimjet wrote: Hi GraceJeanI don't know what version of excel you are using but if you Hi GraceJean I do not know what version of excel you are using but if you protect sheet, you cannot format,change fonts or resize etc... i am using excel3. Regards Cimjet On Tuesday, February 26, 2008 3:24 AM David Biddulph wrote: Which version of Excel are you using? Which version of Excel are you using? The cells that you want to change the values you have unlocked with Format/ Cells/ Unlock? You have gone to Tools/ Protection/ Protect Sheet? The default at that stage (at least in my Excel 2003) is to allow users to select cells, but not to allow them to format cells, so that default will do what you want. Did you change any of the options at that stage? What is your problem having protected the sheet? Is it allowing users to change format although when you protected the sheet you have not selected the option to allow formatting? Is it not allowing users to select cells which you have unlocked? -- David Biddulph "GraceJean Jones" wrote in message ... On Friday, February 29, 2008 1:00 AM GraceJean Jone wrote: Thanks again for your response. Thanks again for your response. In the worksheet I have unlocked certain cells using like you said: Format/ Cells/ Unlock. Then, like you said, I go to Tools/ Protection/ Protect Sheet. If you allow users to "select cells," this option means the users of the worksheet can put their cursor on "locked" cells & select them. This DOES NOT mean that users, once selecting a "locked" cell can do anything with the cell at all. If you do not allow users to "select cells," this then means that the user of the worksheet cannot even get their cursor to move onto a cell that has not been unlocked. If the user trys clicking on a "locked cell" or using the arrows to move around, the cursor will just "jump" over cells that have been "locked" (if the "select cells" if left unchecked). So, in other words, by checking "select cells" all this does is it allows users to get their cursor moved onto or over "locked" cells. If I unlock a cell, users have access to this cell, to input numbers. But the users unfortunately also have full access to change the color, to change the font, to change the borders of the cell, etc. I still cannot figure out a way to unlock a cell so that the only thing users can do in this cell is input a number, & nothing else. Thanks again for your feedback, if you know of something else I can try, that would be great. "David Biddulph" wrote: On Friday, February 29, 2008 2:54 AM David Biddulph wrote: Protect (Lock) Cell Formatting ONLY You failed to answer my first question: "Which version of Excel are you using?" In Excel 2003, and in any other version which I have used, the method which I described does what you ask. I allow users to select cells, and that allows them to change the content of, but not the format of, the unlocked cells (but doesn't allow them to change unlocked cells). In whichever version you have, are you saying that if you allow users to select locked and unlocked cells (the default settings), then the unlocked cells behave exactly the same as the locked cells? [Perhaps you'd better check again that you have actually unlocked the cells that you think you've unlocked?] I would be fascinated to hear if anyone else suffers from these same symptoms. Have a look at Excel help. The topic "About worksheet and workbook protection" and the sub-topic "Protecting worksheet elements" address the relevant area. If your Excel isn't behaving the way that it should do, you may need to reinstall, but I would suggest checking again carefully that you've got the settings correct on your cells and on your worksheet before you resort to that drastic step. -- David Biddulph "GraceJean Jones" GraceJean wrote in message ... On Friday, February 29, 2008 1:07 PM GraceJeanJone wrote: Thanks again for your reply. Thanks again for your reply. Your time in trying to help is very much appreciated. First of all, I was using Excel 2003 at somebody else's computer. I do not have Excel 2003 on my computer to test this all out. However, I went to the MS website that you suggested. I have included a few comments that I think might explain what is going on: * By default the Select locked cells check box is selected. This check box enables users to select cells with the Locked check box selected in the Format Cells dialog box. When the Select locked cells check box is selected, the Select unlocked cells check box is automatically selected. * By default the Select unlocked cells check box is selected. This check box enables users to select cells with the Locked check box cleared in the Format Cells dialog box. When the Select unlocked cells check box is cleared, the Select locked cells check box is automatically cleared. If there are no unlocked cells on a protected sheet and this check box is not selected, users cannot select any cells on the worksheet. * Note: You cannot permit formatting of unlocked cells only. Maybe you can test out the issue I am having (if you have a moment). Unfortunately, this makes me kind of mad, because I'm not at a computer right now that has Excel 2003 running, if I did, I would do this myself. However, when I was at my friend's computer earlier this week, I'm pretty sure I already did what I'm asking below. And...it did not work the way I wanted it to. i.e., my friend wanted me to lock all but certain cells on a worksheet that she was going to give to people to input numbers. My friend was not happy, though, that the users of the worksheet could input numbers just fine into unlocked cells, BUT...they also were able to change the formatting of the cell (color, font, font size, borders, etc.), which she did not want them to be able to do. We tried & tried & tried, we could not figure out a way to do what she wanted. Anyway, if you can try the following, you will see what I am talking about. -Open Excel 2003 -Put your cursor in cell C3 & Highlight the block of cells C3 thru E5. -Select Format/ Cells/ Unlock -Select Tools/ Protection/ Protect Sheet -Make sure the "Select locked cells" box is NOT checked You will then see that all this does it that it does not allow you as the user to get your cursor into any cells other than those that you previously unlocked (i.e., the range of C3 thru E5) If your cursor is in cell E5 & you hit the down arrow, your cursor will not move to cell E6, it will move to cell C3. i.e., the user is not able to select any locked cells whatsoever anywhere on the worksheet. The user will only be able to select cells that have been previously unlocked. If you are able to tell me the further steps at this point that I need to go thru to allow users of this worksheet to be able to input numbers in to the range of cells of C3 thru E5, but NOT be able to change any of the formatting of these cells, please just pass along the additional steps that I need to take to be able to do this. I can call my friend and walk her thru the steps over the phone. Again, your time in helping me out is greatly appreciated. If I can figure this out, this will really be great, and will help us out a lot. Thanks again. "David Biddulph" wrote: On Friday, February 29, 2008 1:46 PM David Biddulph wrote: No more steps needed. No more steps needed. As I said before, in that situation you can put numbers in the unlocked cells but cannot change the formatting. -- David Biddulph On Thursday, June 26, 2008 2:19 PM Sam Hills wrote: Protecting cell formatting I am having a similar problem. I can protect cells so that the user cannot select them, and unprotect the data-entry cells so that the user can enter data. "Format cells" is unchecked, so the "Format cells" option in the right-click menu is greyed out, and the "Cells" option on the "Format" menu is likewise greyed out. However, if the user copies one data-entry cell and pastes it into another, the formatting is pasted too. To demonstrate this: 1. Format column A as text, column B as date and column C as number. On the Protection tab, uncheck "locked". 2. Protect the sheet. Make sure "Format Cells" is unchecked. 3. Select a cell in column A and copy it to the clipboard with Ctrl-C. 4. Paste that cell into cells B1 and C1. 5. Turn sheet protection off. 6. Right-click on cell B1 and select "Format cells". The format has been changed to Text. Likewise for C1. How can I protect cells so that pasting another cell will only paste the source cell's value but not its formatting? On Tuesday, January 27, 2009 4:47 PM abc def wrote: protect cells so that pasting another cell will only paste the source cell's value but not its formatting Hello Mr. Hills, did you ever find a solution to "pasting another cell will only paste the source cell's value but not its formatting" I too am looking for a solution. Thanks! On Sunday, February 08, 2009 3:48 PM erik koepf wrote: How to lock cell format and structe in Excel 2003 I am not as technically sophisticated as most of the people replying to this post, but the problem i was having is exactly as you describe it, and all the proposed solutions seem waaaaaaaay too complicated. I found a simple solution for Excel 2003, so here it is. Sorry if this is obvious: If you have a sheet open and you want to lock the formatting (cell colors, borders, etc.) but want all the data to be able to be manipulated, go to Tools, Protection, Protect Workbook, then select the box that says Structure, and click Ok. All the other posts talk about Protecting Sheet, but that does not work. Protect Workbook is simple and easy. On Wednesday, November 18, 2009 5:17 PM Sascha wrote: David Biddulph is correct If you select ALL of the cells where you want numbers to go by other users, then select FormatCells and under Protection tab UNCHECK Locked - you are required to go to ToolsProtectionProtect Sheet ... and ... VOILA!!! Users are able to enter information in default font but cannot change the size, color, or formatting of the cell... just verified it on MSFT Excel 2003 On Friday, January 01, 2010 7:10 PM Ebrahim Makda wrote: Problem Still exist Hello, My problem is similar / same.. I want to create a worksheet where certain cells are NOT editable AT ALL and other cells where users are able to enter values but NOT change the cell formatting inadvertently.... To replicate my problem :- 1) Create New Workbook 2) cell A1 is the editable cell (all other cells must NOT be editable) 3) cell A1 will be a currency value - set format of cell to accountancy with currency symbol 4) By default all cells are 'locked' on Excel sheets .. cell A1 will need to be 'unlocked' to allow users to enter values once the sheet is protected. therefore, Goto format cells, protection tab, untick 'locked' 5) protect sheet 6) try typing something into cell A2 - due to protection, you are unable to edit - this is ok - that's what i want 5) enter 1:00 into cell A1 This has now changed the cell format for cell A1 to TIME. typing in just the number 1 (for one pound) into cell A1 now, will cause 00:00 to be displayed How can I protect cell A1 so that values can be entered, but cell formatting should never change ? On Friday, January 01, 2010 7:51 PM Ebrahim Makda wrote: Problem Still exist Hello, My problem is similar / same.. I want to create a worksheet where certain cells are NOT editable AT ALL and other cells where users are able to enter values but NOT change the cell formatting inadvertently.... To replicate my problem :- 1) Create New Workbook 2) cell A1 is the editable cell (all other cells must NOT be editable) 3) cell A1 will be a currency value - set format of cell to accountancy with currency symbol 4) By default all cells are 'locked' on Excel sheets .. cell A1 will need to be 'unlocked' to allow users to enter values once the sheet is protected. therefore, Goto format cells, protection tab, untick 'locked' 5) protect sheet 6) try typing something into cell A2 - due to protection, you are unable to edit - this is ok - that's what i want 5) enter 1:00 into cell A1 This has now changed the cell format for cell A1 to TIME. typing in just the number 1 (for one pound) into cell A1 now, will cause 00:00 to be displayed How can I protect cell A1 so that values can be entered, but cell formatting should never change ? On Thursday, January 14, 2010 10:53 AM Lena Yampolsky wrote: The possible solution Check the following: When you protect the workbook (Standard way): Tools/Protection/Protect worksheet/, you should have the whole list of options under: "Allow all users of this worksheet to:" -select locked cells -select unlocked cells - format cells -etc... make sure that "format cells" (as well as other "format" options) are unchecked. I'm surprised that you have this problem, since the default is two first options are checked, and the rest is unchecked. On Friday, March 12, 2010 4:28 AM Clive Sinclair wrote: Further Issues I have similar Issue however to add a posible vital point when locked down formatting can not be altered in the normal way. but can be changed by cut and pasting already formated data into the locked sheet. i.e. if my sheet is formatted to allow the entry of a number into a cell and some numpty cuts and pastes a time entry into that cell the formatting of that locked cell is changed to time. similar happens when data dragging the format will be dragged to regardless of locking and protecting. To fix I think i need some way of forcing cut and paste to default to paste special / Values which prevents the format changing.. Submitted via EggHeadCafe - Software Developer Portal of Choice C# And The Little Iterator That Could http://www.eggheadcafe.com/tutorials...e-iterato.aspx |
#7
|
|||
|
|||
Allow Users to Edit Ranges
I realise this is an old thread but the solution is still worth putting out there ... try the following:
- Lock all the cells that you don't want edited (whether it be formula or formatting) Format Cells/Protection - Go to Tools menu, under 'Protection' select the menu item below 'Protect Sheet' which should be 'Allow Users to Edit Ranges' - In the dialog box select 'New', select the range(s) that you would like to allow the users to change the values of - Protect your sheet That should solve your problems HTH Emma Clive Sinclair wrote: Further Issues 12-Mar-10 I have similar Issue however to add a posible vital point when locked down formatting can not be altered in the normal way. but can be changed by cut and pasting already formated data into the locked sheet. i.e. if my sheet is formatted to allow the entry of a number into a cell and some numpty cuts and pastes a time entry into that cell the formatting of that locked cell is changed to time. similar happens when data dragging the format will be dragged to regardless of locking and protecting. To fix I think i need some way of forcing cut and paste to default to paste special / Values which prevents the format changing.. Previous Posts In This Thread: On Monday, February 25, 2008 4:51 PM GraceJean Jone wrote: Protect (Lock) Cell Formatting ONLY I understand how to protect a worksheet, and I know how to "unlock" certain cells in a worksheet, so that users of the worksheet can enter information into these unlocked cells. I have a worksheet where all I want the users to be able to do in certain cells is to be able to 'put in or insert' numbers. I don't want the users of this worksheet to be able to change the font, font color, font size, borders, etc. In other words, I want to create a worksheet where only numbers can be entered into certain cells, but no changes whatsoever can be made to the cell formatting of these cells. Thanks for your assistance. On Monday, February 25, 2008 5:14 PM David Biddulph wrote: I'm glad to hear that you know how to protect a sheet and how to lock and I am glad to hear that you know how to protect a sheet and how to lock and unlock cells. You therefore know how to answer your question. -- David Biddulph On Monday, February 25, 2008 5:40 PM GraceJeanJone wrote: Thanks for your reply, however, my question was not answered. Thanks for your reply, however, my question was not answered. I want to be able to create cells in a worksheet where users can input numbers, but cannot change any of the formatting of the cell, such as font style, font size, font color, borders, etc. Thanks again. "David Biddulph" wrote: On Monday, February 25, 2008 8:17 PM Cimjet wrote: Hi GraceJeanI don't know what version of excel you are using but if you Hi GraceJean I do not know what version of excel you are using but if you protect sheet, you cannot format,change fonts or resize etc... i am using excel3. Regards Cimjet On Tuesday, February 26, 2008 3:24 AM David Biddulph wrote: Which version of Excel are you using? Which version of Excel are you using? The cells that you want to change the values you have unlocked with Format/ Cells/ Unlock? You have gone to Tools/ Protection/ Protect Sheet? The default at that stage (at least in my Excel 2003) is to allow users to select cells, but not to allow them to format cells, so that default will do what you want. Did you change any of the options at that stage? What is your problem having protected the sheet? Is it allowing users to change format although when you protected the sheet you have not selected the option to allow formatting? Is it not allowing users to select cells which you have unlocked? -- David Biddulph "GraceJean Jones" wrote in message ... On Friday, February 29, 2008 1:00 AM GraceJean Jone wrote: Thanks again for your response. Thanks again for your response. In the worksheet I have unlocked certain cells using like you said: Format/ Cells/ Unlock. Then, like you said, I go to Tools/ Protection/ Protect Sheet. If you allow users to "select cells," this option means the users of the worksheet can put their cursor on "locked" cells & select them. This DOES NOT mean that users, once selecting a "locked" cell can do anything with the cell at all. If you do not allow users to "select cells," this then means that the user of the worksheet cannot even get their cursor to move onto a cell that has not been unlocked. If the user trys clicking on a "locked cell" or using the arrows to move around, the cursor will just "jump" over cells that have been "locked" (if the "select cells" if left unchecked). So, in other words, by checking "select cells" all this does is it allows users to get their cursor moved onto or over "locked" cells. If I unlock a cell, users have access to this cell, to input numbers. But the users unfortunately also have full access to change the color, to change the font, to change the borders of the cell, etc. I still cannot figure out a way to unlock a cell so that the only thing users can do in this cell is input a number, & nothing else. Thanks again for your feedback, if you know of something else I can try, that would be great. "David Biddulph" wrote: On Friday, February 29, 2008 2:54 AM David Biddulph wrote: Protect (Lock) Cell Formatting ONLY You failed to answer my first question: "Which version of Excel are you using?" In Excel 2003, and in any other version which I have used, the method which I described does what you ask. I allow users to select cells, and that allows them to change the content of, but not the format of, the unlocked cells (but doesn't allow them to change unlocked cells). In whichever version you have, are you saying that if you allow users to select locked and unlocked cells (the default settings), then the unlocked cells behave exactly the same as the locked cells? [Perhaps you'd better check again that you have actually unlocked the cells that you think you've unlocked?] I would be fascinated to hear if anyone else suffers from these same symptoms. Have a look at Excel help. The topic "About worksheet and workbook protection" and the sub-topic "Protecting worksheet elements" address the relevant area. If your Excel isn't behaving the way that it should do, you may need to reinstall, but I would suggest checking again carefully that you've got the settings correct on your cells and on your worksheet before you resort to that drastic step. -- David Biddulph "GraceJean Jones" GraceJean wrote in message ... On Friday, February 29, 2008 1:07 PM GraceJeanJone wrote: Thanks again for your reply. Thanks again for your reply. Your time in trying to help is very much appreciated. First of all, I was using Excel 2003 at somebody else's computer. I do not have Excel 2003 on my computer to test this all out. However, I went to the MS website that you suggested. I have included a few comments that I think might explain what is going on: * By default the Select locked cells check box is selected. This check box enables users to select cells with the Locked check box selected in the Format Cells dialog box. When the Select locked cells check box is selected, the Select unlocked cells check box is automatically selected. * By default the Select unlocked cells check box is selected. This check box enables users to select cells with the Locked check box cleared in the Format Cells dialog box. When the Select unlocked cells check box is cleared, the Select locked cells check box is automatically cleared. If there are no unlocked cells on a protected sheet and this check box is not selected, users cannot select any cells on the worksheet. * Note: You cannot permit formatting of unlocked cells only. Maybe you can test out the issue I am having (if you have a moment). Unfortunately, this makes me kind of mad, because I'm not at a computer right now that has Excel 2003 running, if I did, I would do this myself. However, when I was at my friend's computer earlier this week, I'm pretty sure I already did what I'm asking below. And...it did not work the way I wanted it to. i.e., my friend wanted me to lock all but certain cells on a worksheet that she was going to give to people to input numbers. My friend was not happy, though, that the users of the worksheet could input numbers just fine into unlocked cells, BUT...they also were able to change the formatting of the cell (color, font, font size, borders, etc.), which she did not want them to be able to do. We tried & tried & tried, we could not figure out a way to do what she wanted. Anyway, if you can try the following, you will see what I am talking about. -Open Excel 2003 -Put your cursor in cell C3 & Highlight the block of cells C3 thru E5. -Select Format/ Cells/ Unlock -Select Tools/ Protection/ Protect Sheet -Make sure the "Select locked cells" box is NOT checked You will then see that all this does it that it does not allow you as the user to get your cursor into any cells other than those that you previously unlocked (i.e., the range of C3 thru E5) If your cursor is in cell E5 & you hit the down arrow, your cursor will not move to cell E6, it will move to cell C3. i.e., the user is not able to select any locked cells whatsoever anywhere on the worksheet. The user will only be able to select cells that have been previously unlocked. If you are able to tell me the further steps at this point that I need to go thru to allow users of this worksheet to be able to input numbers in to the range of cells of C3 thru E5, but NOT be able to change any of the formatting of these cells, please just pass along the additional steps that I need to take to be able to do this. I can call my friend and walk her thru the steps over the phone. Again, your time in helping me out is greatly appreciated. If I can figure this out, this will really be great, and will help us out a lot. Thanks again. "David Biddulph" wrote: On Friday, February 29, 2008 1:46 PM David Biddulph wrote: No more steps needed. No more steps needed. As I said before, in that situation you can put numbers in the unlocked cells but cannot change the formatting. -- David Biddulph On Thursday, June 26, 2008 2:19 PM Sam Hills wrote: Protecting cell formatting I am having a similar problem. I can protect cells so that the user cannot select them, and unprotect the data-entry cells so that the user can enter data. "Format cells" is unchecked, so the "Format cells" option in the right-click menu is greyed out, and the "Cells" option on the "Format" menu is likewise greyed out. However, if the user copies one data-entry cell and pastes it into another, the formatting is pasted too. To demonstrate this: 1. Format column A as text, column B as date and column C as number. On the Protection tab, uncheck "locked". 2. Protect the sheet. Make sure "Format Cells" is unchecked. 3. Select a cell in column A and copy it to the clipboard with Ctrl-C. 4. Paste that cell into cells B1 and C1. 5. Turn sheet protection off. 6. Right-click on cell B1 and select "Format cells". The format has been changed to Text. Likewise for C1. How can I protect cells so that pasting another cell will only paste the source cell's value but not its formatting? On Tuesday, January 27, 2009 4:47 PM abc def wrote: protect cells so that pasting another cell will only paste the source cell's value but not its formatting Hello Mr. Hills, did you ever find a solution to "pasting another cell will only paste the source cell's value but not its formatting" I too am looking for a solution. Thanks! On Sunday, February 08, 2009 3:48 PM erik koepf wrote: How to lock cell format and structe in Excel 2003 I am not as technically sophisticated as most of the people replying to this post, but the problem i was having is exactly as you describe it, and all the proposed solutions seem waaaaaaaay too complicated. I found a simple solution for Excel 2003, so here it is. Sorry if this is obvious: If you have a sheet open and you want to lock the formatting (cell colors, borders, etc.) but want all the data to be able to be manipulated, go to Tools, Protection, Protect Workbook, then select the box that says Structure, and click Ok. All the other posts talk about Protecting Sheet, but that does not work. Protect Workbook is simple and easy. On Wednesday, November 18, 2009 5:17 PM Sascha wrote: David Biddulph is correct If you select ALL of the cells where you want numbers to go by other users, then select FormatCells and under Protection tab UNCHECK Locked - you are required to go to ToolsProtectionProtect Sheet ... and ... VOILA!!! Users are able to enter information in default font but cannot change the size, color, or formatting of the cell... just verified it on MSFT Excel 2003 On Friday, January 01, 2010 7:10 PM Ebrahim Makda wrote: Problem Still exist Hello, My problem is similar / same.. I want to create a worksheet where certain cells are NOT editable AT ALL and other cells where users are able to enter values but NOT change the cell formatting inadvertently.... To replicate my problem :- 1) Create New Workbook 2) cell A1 is the editable cell (all other cells must NOT be editable) 3) cell A1 will be a currency value - set format of cell to accountancy with currency symbol 4) By default all cells are 'locked' on Excel sheets .. cell A1 will need to be 'unlocked' to allow users to enter values once the sheet is protected. therefore, Goto format cells, protection tab, untick 'locked' 5) protect sheet 6) try typing something into cell A2 - due to protection, you are unable to edit - this is ok - that's what i want 5) enter 1:00 into cell A1 This has now changed the cell format for cell A1 to TIME. typing in just the number 1 (for one pound) into cell A1 now, will cause 00:00 to be displayed How can I protect cell A1 so that values can be entered, but cell formatting should never change ? On Friday, January 01, 2010 7:51 PM Ebrahim Makda wrote: Problem Still exist Hello, My problem is similar / same.. I want to create a worksheet where certain cells are NOT editable AT ALL and other cells where users are able to enter values but NOT change the cell formatting inadvertently.... To replicate my problem :- 1) Create New Workbook 2) cell A1 is the editable cell (all other cells must NOT be editable) 3) cell A1 will be a currency value - set format of cell to accountancy with currency symbol 4) By default all cells are 'locked' on Excel sheets .. cell A1 will need to be 'unlocked' to allow users to enter values once the sheet is protected. therefore, Goto format cells, protection tab, untick 'locked' 5) protect sheet 6) try typing something into cell A2 - due to protection, you are unable to edit - this is ok - that's what i want 5) enter 1:00 into cell A1 This has now changed the cell format for cell A1 to TIME. typing in just the number 1 (for one pound) into cell A1 now, will cause 00:00 to be displayed How can I protect cell A1 so that values can be entered, but cell formatting should never change ? On Thursday, January 14, 2010 10:53 AM Lena Yampolsky wrote: The possible solution Check the following: When you protect the workbook (Standard way): Tools/Protection/Protect worksheet/, you should have the whole list of options under: "Allow all users of this worksheet to:" -select locked cells -select unlocked cells - format cells -etc... make sure that "format cells" (as well as other "format" options) are unchecked. I'm surprised that you have this problem, since the default is two first options are checked, and the rest is unchecked. On Friday, March 12, 2010 4:28 AM Clive Sinclair wrote: Further Issues I have similar Issue however to add a posible vital point when locked down formatting can not be altered in the normal way. but can be changed by cut and pasting already formated data into the locked sheet. i.e. if my sheet is formatted to allow the entry of a number into a cell and some numpty cuts and pastes a time entry into that cell the formatting of that locked cell is changed to time. similar happens when data dragging the format will be dragged to regardless of locking and protecting. To fix I think i need some way of forcing cut and paste to default to paste special / Values which prevents the format changing.. On Friday, April 23, 2010 8:15 PM Emma Farrell wrote: Allow Users to Edit Ranges I realise this is an old thread but the solution is still worth putting out there ... try the following: - Lock all the cells that you don't want edited (whether it be formula or formatting) Format Cells/Protection - Go to Tools menu, under 'Protection' select the menu item below 'Protect Sheet' which should be 'Allow Users to Edit Ranges' - In the dialog box select 'New', select the range(s) that you would like to allow the users to change the values of - Protect your sheet That should solve your problems HTH Emma Submitted via EggHeadCafe - Software Developer Portal of Choice Book Review: C# 4.0 In a Nutshell [O'Reilly] http://www.eggheadcafe.com/tutorials...c-40-in-a.aspx |
#8
|
|||
|
|||
Allow Users to Edit Ranges
I should also mention that at the bottom of the 'Allow Users to Edit Ranges' dialog box in a check box that can copy the permissions to new workbooks ...
Emma Emma Farrell wrote: Allow Users to Edit Ranges 23-Apr-10 I realise this is an old thread but the solution is still worth putting out there ... try the following: - Lock all the cells that you don't want edited (whether it be formula or formatting) Format Cells/Protection - Go to Tools menu, under 'Protection' select the menu item below 'Protect Sheet' which should be 'Allow Users to Edit Ranges' - In the dialog box select 'New', select the range(s) that you would like to allow the users to change the values of - Protect your sheet That should solve your problems HTH Emma Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice BOOK REVIEW: Effective C#, Second Edition [Addison Wesley] http://www.eggheadcafe.com/tutorials...fective-c.aspx |
#9
|
|||
|
|||
Allow Users to Edit Ranges
I should also mention that at the bottom of the 'Allow Users to Edit Ranges' dialog box in a check box that can copy the permissions to new workbooks ...
Emma Emma Farrell wrote: Allow Users to Edit Ranges 23-Apr-10 I realise this is an old thread but the solution is still worth putting out there ... try the following: - Lock all the cells that you don't want edited (whether it be formula or formatting) Format Cells/Protection - Go to Tools menu, under 'Protection' select the menu item below 'Protect Sheet' which should be 'Allow Users to Edit Ranges' - In the dialog box select 'New', select the range(s) that you would like to allow the users to change the values of - Protect your sheet That should solve your problems HTH Emma Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice BizTalk: Incorporating conditional If / Else Functoid Logic in a map. http://www.eggheadcafe.com/tutorials...rating-co.aspx |
Thread Tools | |
Display Modes | |
|
|