 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / September 2007Worksheet Change event code, but retain Undo?
Hi. A team here wanted some formatting, dependent upon 4 other cells, in an OR formula. if =or(f33<f39,f34<f40) is true, they want cell f1 to have a red background. If it's false, it has the default blue background. Since conditional formatting is only dependent upon the value of the cell to which the cell is being applied, the way I saw to do this was with some custom code. I put some code into the worksheet_change event which does the formatting fine. But, now the Undo button is not available... running the custom code takes away the possibility of Undo. (no particular surprise). Is there a way to have both? Thanks, Mark Conditional formatting can be dependant on whatever you want it to be. Change from cell value to Formula and go to town with it... =or(f33<f39,f34<f40) Is a perfectly valid conditional format in any cell you want to put it... To answer your other question code execution wipes out the undo and there is no way around it...  SignatureHTH... Jim Thomlinson > Hi. > [quoted text clipped - 18 lines] > Thanks, > Mark But see http://www.j-walk.com/ss/excel/tips/tip23.htm for some workarounds. > To answer your other question code execution wipes out the undo and there is > no way around it... That undoes the subroutine but what about the changes that the user made prior to running the code. That is lost forever isn't it? I was asuming that that is what the user meant by undo... SignatureHTH... Jim Thomlinson > But see > [quoted text clipped - 4 lines] > > To answer your other question code execution wipes out the undo and there is > > no way around it... > That undoes the subroutine but what about the changes that the user made > prior to running the code. That is lost forever isn't it? I was asuming that > that is what the user meant by undo... It was. The formula conditional formatting that you mentioned solves the specific problem, but general info is good, too. Thanks. Yes, unless your code also saves a "stack" of previous changes, too (it's a PITA, but it can be done). > That undoes the subroutine but what about the changes that the user made > prior to running the code. That is lost forever isn't it? I was asuming that > that is what the user meant by undo... I have never bothered trying to save the undo stack. For one project I did maintain a listing af all of the changes made by the user and gave them some ability to roll back their changes but it was kinda crude and a whole pile of work. SignatureHTH... Jim Thomlinson > Yes, unless your code also saves a "stack" of previous changes, too > (it's a PITA, but it can be done). > > > That undoes the subroutine but what about the changes that the user made > > prior to running the code. That is lost forever isn't it? I was asuming that > > that is what the user meant by undo... I assume you are not saying you can access the Undo history programmatically and save it before the macro wipes it out? If you are saying you can grab the Excel built in undo history and later restore it or otherwise interpret it and "undo", what objects or API is involved? SignatureRegards, Tom Ogilvy > Yes, unless your code also saves a "stack" of previous changes, too > (it's a PITA, but it can be done). > > > That undoes the subroutine but what about the changes that the user made > > prior to running the code. That is lost forever isn't it? I was asuming that > > that is what the user meant by undo... No, I'm saying you have to generate your own "stack" via Workbook_SheetChange/SelectionChange/etc and save it somewhere. The two times I've done it, it's been a real kludge and the client has paid dearly for the privilege. They were happy, though. > I assume you are not saying you can access the Undo history programmatically > and save it before the macro wipes it out? If you are saying you can grab > the Excel built in undo history and later restore it or otherwise interpret > it and "undo", what objects or API is involved? Thanks for the response and glad to see it was profitable <g> SignatureRegards, Tom Ogilvy > No, I'm saying you have to generate your own "stack" via > Workbook_SheetChange/SelectionChange/etc and save it somewhere. [quoted text clipped - 6 lines] > > the Excel built in undo history and later restore it or otherwise interpret > > it and "undo", what objects or API is involved? ...it's been a real kludge and the client has paid dearly for the privilege So it was not just me hacking something together for hours on end... oddly enough I feel better. Not much better... but better... SignatureHTH... Jim Thomlinson > No, I'm saying you have to generate your own "stack" via > Workbook_SheetChange/SelectionChange/etc and save it somewhere. [quoted text clipped - 6 lines] > > the Excel built in undo history and later restore it or otherwise interpret > > it and "undo", what objects or API is involved? okay, thanks. I see what you are saying. Got it. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.