saving the clipboard 1

[RobBroekhuis]

I have an Excel event handler routine (SheetChange) that uses copy, and therefore overwrites the clipboard. I'm trying to save and restore the clipboard, so that this is transparent to the user, using the following code:

dim ClipSave as DataObject

[in Workbook_Open:]
set ClipSave = new DataObject

Private Sub SaveClipBoard()
   If Application.CutCopyMode > 0 Then
      ClipSave.GetFromClipboard
   End If
End Sub
Private Sub RestoreClipBoard()
   ClipSave.PutInClipboard
   ClipSave.Clear
End Sub

I get no errors, and at the end of the event the "Paste" option is available on the Edit menu, but when I try to use it, Excel tells me it can't paste the data. I was able to get the concept to work while playing around in direct mode, with both single-cell and multi-cell selections. What's wrong with the code above?
Thanks
Rob

 

[euskadi]

I tried to duplicate what you're doing here, and the simple act of running a macro (or doing the sheet_change event) seems to be dumping the clipboard contents.

Your procedure might be working fine, except that when it goes to get the clipboard contents, the clipboard is already empty.

 

[RobBroekhuis]

Ah - good thinking. That would explain things.
In that case, let me challenge the forum with the larger picture, and see if there is an alternative solution.
My event handler detects when entries in a log are changed, and writes the changed fields to a change log on a different worksheet. The actual writing of the change log (for any entries on the active sheet) happens on a Workbook_SheetDeactivate event. So now if my user makes some changes, and then decides he wants to copy some information from that sheet to a different one, he selects and copies that information, and proceeds to the desired tab - BAM! Event handler takes over, and all of a sudden there is nothing to copy. User is confused. Not a huge deal, but if I can make it more elegant, I'd like to try.
Thanks for your input!
Rob

 

[euskadi]

You might try using the sheet_change event instead.

Matter of fact, you might _need_ to do that. I just ran a quick test with the deactivate event. Did you know that the deactivate event doesn't run when you close the workbook? At least it didn't when I tested it. You might be missing some entries on your log if the user makes a change and then closes the workbook.

 

[RobBroekhuis]

Euskadi,
Good catch. Actually, I trigger the copying of the log on either Workbook_SheetDeactivate or Workbook_BeforeSave, which takes care of the closing of the workbook. I have a Sheet_Change event handler, but since I only want to write ONE change log entry per changed or added record (row), I keep track of which records have changed and don't write the log entries until the user is done with the particular table.
Rob