? Sure it is,
It's just a bit of trouble. Actually, IsItPossible is almost always = True. Even if drastic measuers are required or no one knows exactly how it's possible. But most requests that come up are possible in some relatively understood way. 1. Any macro can be undo-able, but you usually have to do the undoing yourself in your macro. This means your macro needs to do 3 things: (a) keep a careful record of all changes it makes and how to undo them (b) make the changes and (c) restore the changes when requested. Excel has the Application.OnUndo method for your macro to notify Excel that it has this capability and it specifies the macro/procedure name for Excel to call when the user hits Undo. See http://j-walk.com/ss/excel/tips/tip23.htm. 2. If your macro doesn't directly change the worksheet, your macro does not interfere with undo capability (in general). You can take advantage of this by using Application.SendKeys to put keyboard shortcuts in the keyboard buffer that cause Excel to do stuff after your macro ends. Pros usually avoid this method since there is not a 100% guarantee that the user might not press other keys at the same time or that the wrong window might have the focus, or that the user had reassigned the keyboard shortcuts. But it is a simple option. In your case, there are keyboard shortcuts for the stuff your macro does, so this method is viable. . For option #1, see the linked tip for ideas on how to implement it. . For option #2, in my tests the following rewrites of your macro all worked: Sub daniyal() ' This version is preferred - the others need the macro to pause a second With Application ' User has hand on ctrl key from macro shortcut, but does not seem to interfere with this combo ' "+{F10}v" is Shift-F10 for context menu, then v for paste values (unlisted). ' "^+!" is Ctrl-Shift-! for the number format #,##0.00 If .CutCopyMode Then .SendKeys "+{F10}v^+!" End With End Sub Sub daniyal() With Application .Wait Now + 1 / 86400 ' one second for user to release ctrl key ' "%esv~" is Alt-e (edit), s (paste special), v (values), enter (ok). ' "^+!" is Ctrl-Shift-! for the number format #,##0.00 If .CutCopyMode Then .SendKeys "%esv~^+!" End With End Sub Sub daniyal() ' This version for Excel 2007+ only With Application .Wait Now + 1 / 86400 ' one second for user to release ctrl key ' "%hvv" is Alt-h (home), v (paste), v (values). ' "^+!" is Ctrl-Shift-! for the number format #,##0.00 If .CutCopyMode Then .SendKeys "%hvv^+!" End With End Sub Undo worked after running the macro, but undo had to be pressed twice since two actions are taken (paste special and format). P.S. In your original macro I'm not sure why you are assigned the return value of PasteSpecial to selection.HasFormula... ? Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Wednesday, March 07, 2012 5:39 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ modification in paste special macro. no Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: danial mansoor <mailto:danial_...@hotmail.com> Sent: Tuesday, March 06, 2012 10:41 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ modification in paste special macro. i used this macro and assigned it a shortcut key for my conveniance for paste special values,but i can not undo last actions is that possible to undo last 2 actions after modification of this posible? is that possible to modify and get desired result and undo last 2 actions only? how is that possible? Sub daniyal() Application.ScreenUpdating = False On Error Resume Next Selection.HasFormula = Selection.PasteSpecial(xlPasteValues) Selection.NumberFormat = "#,##0.00" Application.CutCopyMode = False Application.ScreenUpdating = True End Sub -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
