Posted: 3/26/2013 5:44:08 PM EDT
|
I have a conditional format set up on Excel 2010 that affects a document. I want users to be able to enter data and manipulate cells. However, the paste/cut/insert cells/delete rows functions (common tools) ruins the conditional formatting ranges. IE: While routinely fixing up the document, I want to cut a cell's information and move it to another cell. Standard stuff someone might do. But now the cell that was cut is void of any conditional formatting rule and must be reapplied. Locking/Protecting cells is inconvenient because it prevents someone from manipulating cell data. I have searched for macros to help with this but come up short. The only thing I can think of doing is disabling cut/copy/paste/insert cells/delete rows/etc. but I'm not savvy enough to do that yet. Any ideas?
|
| Create another worksheet that allows users to enter values that feed into the spreadsheet that has the conditional formatting and values? You're not going to win with the situation you describe... you can't allow users to manipulate cell formatting and data and expect consistent output... |
|
Quoted:
I have a conditional format set up on Excel 2010 that affects a document. I want users to be able to enter data and manipulate cells. However, the paste/cut/insert cells/delete rows functions (common tools) ruins the conditional formatting ranges. IE: While routinely fixing up the document, I want to cut a cell's information and move it to another cell. Standard stuff someone might do. But now the cell that was cut is void of any conditional formatting rule and must be reapplied. Locking/Protecting cells is inconvenient because it prevents someone from manipulating cell data. I have searched for macros to help with this but come up short. The only thing I can think of doing is disabling cut/copy/paste/insert cells/delete rows/etc. but I'm not savvy enough to do that yet. Any ideas? How are you cutting and pasting? Just paste as values or formula only and it won affect your formatting |
I'm not a fan of protecting sheets, but I'm wondering if a decent solution would be to create a macro and link it to a simple button like "Apply Formatting" that the user would activate AFTER doing all their manipulation. That said, I don't have the code for you  Here's some stock code to play with: From: http://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code <code>Sub setCondFormat() |
|
Quoted:
I'm not a fan of protecting sheets, but I'm wondering if a decent solution would be to create a macro and link it to a simple button like "Apply Formatting" that the user would activate AFTER doing all their manipulation. That said, I don't have the code for you You don't need code for something that simple. Just use the record macro option. |
| Try as another suggested and add a spreadsheet tab in your workbook for data entry and link those data entry cells to your presentation tab with the conditional formatting. Linking is much easier than copy-cut-paste and doesn't disturb formatting on the linked presentation page. |
|
Quoted: Quoted: I have a conditional format set up on Excel 2010 that affects a document. I want users to be able to enter data and manipulate cells. However, the paste/cut/insert cells/delete rows functions (common tools) ruins the conditional formatting ranges. IE: While routinely fixing up the document, I want to cut a cell's information and move it to another cell. Standard stuff someone might do. But now the cell that was cut is void of any conditional formatting rule and must be reapplied. Locking/Protecting cells is inconvenient because it prevents someone from manipulating cell data. I have searched for macros to help with this but come up short. The only thing I can think of doing is disabling cut/copy/paste/insert cells/delete rows/etc. but I'm not savvy enough to do that yet. Any ideas? How are you cutting and pasting? Just paste as values or formula only and it won affect your formatting The problem is that "I" know what to do to prevent the excel document from screwing up. But I can't expect Regular Joe from doing what is instinctual to his limited Excel background. |
|
Quoted: Create another worksheet that allows users to enter values that feed into the spreadsheet that has the conditional formatting and values? You're not going to win with the situation you describe... you can't allow users to manipulate cell formatting and data and expect consistent output... This makes sense. Thanks. I was just wondering if there was any way to do it on a single spreadsheet. |
|
The only thing I can think of off the top of my head is to copy and paste, then delete the contents of the original cells. Deleting doesn't remove formatting like cut does. Adding rows should work fine as long as they're inserted within the range of the other formatted rows. |
|
Quoted: I don't use 2010 but I believe protecting the sheet will protect formatting even if the cells are unlocked. Leaving the cells unlocked but protecting the sheet to prevent a user from formatting still doesn't protect the "cut" function or pasting from outside of the spreadsheet from ruining the format. You would think the designers would have thought about this dilemma.
|
| If you want to link to formatted cells without adding another tab, it's easily doable. And if you need to print your document and don't want to see the section where data is input, hide the rows or columns before printing, then unhide when done to allow users to enter data in the appropriate cells. |
|
Quoted:
Quoted:
Create another worksheet that allows users to enter values that feed into the spreadsheet that has the conditional formatting and values? You're not going to win with the situation you describe... you can't allow users to manipulate cell formatting and data and expect consistent output... This makes sense. Thanks. I was just wondering if there was any way to do it on a single spreadsheet. Yea, have input cells to the side. 
|
|
Quoted: Quoted: Quoted: Create another worksheet that allows users to enter values that feed into the spreadsheet that has the conditional formatting and values? You're not going to win with the situation you describe... you can't allow users to manipulate cell formatting and data and expect consistent output... This makes sense. Thanks. I was just wondering if there was any way to do it on a single spreadsheet. Yea, have input cells to the side. Hah. I will just link spreadsheets. Otherwise one spreadsheet will be too cluttered. Thanks guys. |
|
It seems linking between worksheets doesn't work either. If you cut/delete rows on the editable worksheet, you force a #REF! error because the link is removed. Inserting cells is unrecognized on the linked page. Again, why did Microsoft overlook this simple problem?
 |
|
Quoted:
I have a conditional format set up on Excel 2010 that affects a document. I want users to be able to enter data and manipulate cells. However, the paste/cut/insert cells/delete rows functions (common tools) ruins the conditional formatting ranges. IE: While routinely fixing up the document, I want to cut a cell's information and move it to another cell. Standard stuff someone might do. But now the cell that was cut is void of any conditional formatting rule and must be reapplied. Locking/Protecting cells is inconvenient because it prevents someone from manipulating cell data. I have searched for macros to help with this but come up short. The only thing I can think of doing is disabling cut/copy/paste/insert cells/delete rows/etc. but I'm not savvy enough to do that yet. Any ideas? Try "paste special"? And select "values" from the drop-down, maybe. Are you trying to preserve format in the cell from which you cut the data, or in the target of the paste? |
|
Quoted: Quoted: I have a conditional format set up on Excel 2010 that affects a document. I want users to be able to enter data and manipulate cells. However, the paste/cut/insert cells/delete rows functions (common tools) ruins the conditional formatting ranges. IE: While routinely fixing up the document, I want to cut a cell's information and move it to another cell. Standard stuff someone might do. But now the cell that was cut is void of any conditional formatting rule and must be reapplied. Locking/Protecting cells is inconvenient because it prevents someone from manipulating cell data. I have searched for macros to help with this but come up short. The only thing I can think of doing is disabling cut/copy/paste/insert cells/delete rows/etc. but I'm not savvy enough to do that yet. Any ideas? Try "paste special"? And select "values" from the drop-down, maybe. Are you trying to preserve format in the cell from which you cut the data, or in the target of the paste? Both. I think I have found out how to make linking to another worksheet reference dynamically by using the INDIRECT function. So if cells are added/deleted/cut on the original sheet, the second sheet will simply keep track of what it sees versus mapping individual cells. For instance on Sheet2: =INDIRECT("Sheet1!C3") instead of normal linking =Sheet1!C4 I've been playing with it for a while. Will let you all know how it goes. |
|
Quoted: You are talking data entry AND cutting and pasting? You need Access, not Excel. I'm just trying to prevent users from screwing up the conditional formatting on a spreadsheet. I have found the workaround by linking spreadsheets via the INDIRECT formula. One page is protected and for viewing purposes (which has the conditional formatting), the other page is simply data input. They can cut/paste as much as they want. |
|
Quoted:
Quoted:
You are talking data entry AND cutting and pasting? You need Access, not Excel. I'm just trying to prevent users from screwing up the conditional formatting on a spreadsheet. I have found the workaround by linking spreadsheets via the INDIRECT formula. One page is protected and for viewing purposes (which has the conditional formatting), the other page is simply data input. They can cut/paste as much as they want. I'm just saying Access is all about data entry and manipulation with strong data protection (tables). Make the leap from Excel to Access. You can import your excel data into it. |
|
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting? You need Access, not Excel. Excel is perfectly capable of doing what he wants. So is a skateboard to get to work. Access compares well to a skateboard - I guess if you can't use anything else it might get the job done. |
|
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting? You need Access, not Excel. Excel is perfectly capable of doing what he wants. So is a skateboard to get to work. Access compares well to a skateboard - I guess if you can't use anything else it might get the job done. Multiple endusers punching in data is asking for a load of butthurt when using Excel. That is NOT its purpose. |
|
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting? You need Access, not Excel. Excel is perfectly capable of doing what he wants. So is a skateboard to get to work. Access compares well to a skateboard - I guess if you can't use anything else it might get the job done. Multiple endusers punching in data is asking for a load of butthurt when using Excel. That is NOT its purpose. Excel can do it just fine. Use a data entry spreadsheet and a separate, formatted spreadsheet that the users don't have access to that pulls the data from the other(s) and formats it. I wrote a couple of spreadsheets to do nearly exactly that for several hundred users and constant data entry on my ship, I think it took me an hour or so to put it together, and I don't actually know VBA at all. |
|
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting? You need Access, not Excel. Excel is perfectly capable of doing what he wants. So is a skateboard to get to work. Access compares well to a skateboard - I guess if you can't use anything else it might get the job done. Multiple endusers punching in data is asking for a load of butthurt when using Excel. That is NOT its purpose. Excel can do it just fine. Use a data entry spreadsheet and a separate, formatted spreadsheet that the users don't have access to that pulls the data from the other(s) and formats it. I wrote a couple of spreadsheets to do nearly exactly that for several hundred users and constant data entry on my ship, I think it took me an hour or so to put it together, and I don't actually know VBA at all. You just MacGuyvered excel into a weak version of Access, but with very little utility and use. Imagine customizing your input and querying your tables on the fly. And even further, make nice reports anyone can read. Nifty! |
|
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting? You need Access, not Excel. Excel is perfectly capable of doing what he wants. So is a skateboard to get to work. Access compares well to a skateboard - I guess if you can't use anything else it might get the job done. Multiple endusers punching in data is asking for a load of butthurt when using Excel. That is NOT its purpose. Excel can do it just fine. Use a data entry spreadsheet and a separate, formatted spreadsheet that the users don't have access to that pulls the data from the other(s) and formats it. I wrote a couple of spreadsheets to do nearly exactly that for several hundred users and constant data entry on my ship, I think it took me an hour or so to put it together, and I don't actually know VBA at all. You just MacGuyvered excel into a weak version of Access, but with very little utility and use. Imagine customizing your input and querying your tables on the fly. And even further, make nice reports anyone can read. Nifty! I'm thoroughly familiar with access, I built databases and trouble call tracking systems with it. It's a piece of shit, most people don't have it because it isn't in the default install of office, and excel quite frankly is faster and more flexible for simple tasks like the op is doing. |
|
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting? You need Access, not Excel. Excel is perfectly capable of doing what he wants. So is a skateboard to get to work. Access compares well to a skateboard - I guess if you can't use anything else it might get the job done. Multiple endusers punching in data is asking for a load of butthurt when using Excel. That is NOT its purpose. Excel can do it just fine. Use a data entry spreadsheet and a separate, formatted spreadsheet that the users don't have access to that pulls the data from the other(s) and formats it. I wrote a couple of spreadsheets to do nearly exactly that for several hundred users and constant data entry on my ship, I think it took me an hour or so to put it together, and I don't actually know VBA at all. You just MacGuyvered excel into a weak version of Access, but with very little utility and use. Imagine customizing your input and querying your tables on the fly. And even further, make nice reports anyone can read. Nifty! I'm thoroughly familiar with access, I built databases and trouble call tracking systems with it. It's a piece of shit, most people don't have it because it isn't in the default install of office, and excel quite frankly is faster and more flexible for simple tasks like the op is doing. Access is the shittiest of all databases, but mostly the .mil community is stuck with it. Excel is nice for simple tasks, but with multiple users in a shared workbook is just asking for trouble. I've built worksheets since excel existed. |
|
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting? You need Access, not Excel. Excel is perfectly capable of doing what he wants. So is a skateboard to get to work. Access compares well to a skateboard - I guess if you can't use anything else it might get the job done. Multiple endusers punching in data is asking for a load of butthurt when using Excel. That is NOT its purpose. Excel can do it just fine. Use a data entry spreadsheet and a separate, formatted spreadsheet that the users don't have access to that pulls the data from the other(s) and formats it. I wrote a couple of spreadsheets to do nearly exactly that for several hundred users and constant data entry on my ship, I think it took me an hour or so to put it together, and I don't actually know VBA at all. You just MacGuyvered excel into a weak version of Access, but with very little utility and use. Imagine customizing your input and querying your tables on the fly. And even further, make nice reports anyone can read. Nifty! I'm thoroughly familiar with access, I built databases and trouble call tracking systems with it. It's a piece of shit, most people don't have it because it isn't in the default install of office, and excel quite frankly is faster and more flexible for simple tasks like the op is doing. Access is the shittiest of all databases, but mostly the .mil community is stuck with it. Excel is nice for simple tasks, but with multiple users in a shared workbook is just asking for trouble. I've built worksheets since excel existed. That's why you don't use shared worksheets. |
|
Alright. I've spent the the last 24 hours (minus maybe about 7 hours of sleep) reading and practicing VBA code. After a while of mastering INDIRECT functions across two spreadsheets (one for editing, and one for protected viewing), I then moved on to command buttons that caused a very handy sort function (hides all rows that do not contain a highlighted cell date, IE yellow cell for 60 days out and red cell for 30 days out and earlier), and one button to simply unhide all cells. Took a while to get the code right so it would be instant (at first, I was forcing it to perform multiple IF checks cell by cell which took FOREVER). Then once that was done, I wasn't satisfied with what I accomplished. And heeding advice from someone in here earlier who mentioned to force formatting via a macro on the one page: I played around with it until... badabing, badaboom! Users can now manipulate the data as much as they want without fear of screwing anything up. And I don't have to have a separate linked page or any protected page nonsense. As a non-desk sitting .mil guy, this is almost like Prometheus delivering fire to mankind. Now I can keep track of my troops' shit easily and pass this along our unit so we aren't re-inventing the wheel every Ops meeting. It's amazing how Office illiterate we are when this could help cut our admin-wasting-time in half. For anyone interested in what the code is: EDIT: The reason the hide rows code uses the dates versus color of cell is due to the fact that I couldn't get it to hide based on color back when I was using conditional formatting. It may or may not be possible now that I opted out of conditional formatting for the ChangeEvent highlighting. Private Sub Worksheet_Change(ByVal Target As Range) |