Running macros/events clears undo history and clipboard

[Creisti86]

I don't know if this problem has already been solved on this forum, but I haven't found the solution here.

I have a select event on a worksheet, after the select event runs, the clipboard, and the undo history is deleted.
This is very annoying as these facilities are very usefull.
Is there any way to disable this thing? Or do I have to write code that does the same thing (events for ctrl+C pressed, copying data, event for ctrl+V, saving undo data on cell value change, etc.)
The second solution would be somewhat hard to implement, and I would also be interested if such things have already been done.
Thank you.

[dominicb]

Good afternoon Creisti86

...and welcome to the forum!!

Macros can have a couple of unfortunate side effects. The will clear the undo stack and may clear the clipboard, depending on what it does. The answer is pretty much get used to it. Microsofts answer is to provide the user with the Application.OnUndo construct within VBA which will allow the user to provide their own code for an undo routine - but just for one level. Not that generous really.

There is a brief discussion about taking the subject further here, don't know if that's of any use to you.

http://www.mrexcel.com/board2/viewtopic.php?t=292203

HTH

DominicB

[Creisti86]

The macro that I run verifies if the selected column should contain a date (eg.: If ActiveCell.Column = 2 ). If it should, it makes a calendar control visible, for writing easy, and format-safe date. Only the "Calendar.Visible = True" statement deletes the clipboard, and even an empty event deletes undo history.
I tried inserting this code at the being and and at the end of an event, in order to "Backup" the clipboard against deleting:

Please Login or Register  to view this content.

<actions>

Please Login or Register  to view this content.

but this causes an error: "PutInClipboard not implemented"

Thank you for the link though, I would have tried to use some of that code, and maybe adapt it for copy+paste also, but...:
I'm not trying to undo macros effects, as there are no visible changes, I just want to be able to undo the changes done directly, without any macro.
Also, I tried to make those "ctrl+C" pressed events but it seems that worksheets don't have keypress events, those are only form events, and this kind of sucks. :D

[Creisti86]

Ok, what i have done is to make two labels : "copy" and "paste" apear near the active cell, when copy is clicked, the value and the formating are copied in a static array of "SavedRanges" that is actually a user defined type witch contains a Value as a Variant, a x and y coordinates of the cell from the first copied cell, and a format as a string, witch is actually the NumberFormat property of a range object. When copy is clicked it automatically static allocates the array to the number of cells selected (note here that you can only static allocate an array of max 32768 items), and copies the information formating and coordinates to each item. When paste is clicked it gets the date from that array, and puts it from the active cell,to the cell specified by the ActiveCell's position plus the coordinates for the pasted cell. It works fine, about as fast as the normal copy-paste, the troubles are: limited number of cells pasting (that is a problem when you select entire columns), it is not compatible with the normal copy-paste, if you want to copy from/to another worksheet, you will have to use a normal copy paste, with special attention to not selecting any other cell in the worksheet with events, the last problem is with the usage, clicking a small label near the active cell is not that easy as pressing ctrl+c (also because one is always very used to it), and the labels may also make it harder to select the cell near by, so I've added the possibility of displaying the labels in a fixed place.

the ModuleClipboard:

Please Login or Register  to view this content.

and its usage in the worksheet module:

Please Login or Register  to view this content.

For usage of the combination keys ctrl+c/v, i was thinking of creating an invisible form that takes focus when a cell is selected, and its only purpose to be for listening for key press, but that would be tricky to solve the way to move the focus from the form to the worksheet if other keys are pressed (to modify the cell or to select another). So I'm not sure this would work.

I have also used a modified version of the code in that link above to undo a macro I was using that modified cells, but I still can't think of a way to undo normal actions (other than saving from time to time, and undoing to a specified time, but this would be very resource and time consuming so I've let the user choose when to save ). That is because when someone modifies a cell, the event triggered at the change already finds the modified value, would have been usefull a "before change" event.