VBA empties clipboard

[jeffbert]

I have the following two events in my workbook to turn calculation on and off
when the workbook is activated and deactivated.

Private Sub Workbook_Activate()
Application.Calculation = xlCalculationManual
End Sub

Private Sub Workbook_Deactivate()
Application.Calculation = xlCalculationAutomatic
End Sub

The problem is that this prevents users from copying from another Excel
workbook and pasting into the one with the code. I am assuming it is because
when the VBA fires, it is emptying the clipboard.

Is there a way to make my workbook calculate manually, while leaving any
other workbooks that are open calculate automatically, and be able to paste
back and forth between workbooks?

 

[Dave Peterson]

That's one of the problems with macros. They can kill the clipboard.

You could check to see if something is in the clipboard and not touch the
calculation mode. But then you do lose what you want to do.

 

[jeffbert]

Dave

Thanks for the response, unfortunately it was the answer I was dreading. I
was thinking there might be a way to "capture" the clipboard, then switch the
calculation, then re-instate what was captured from the clipboard. Thanks for
the help, I will work on another solution.

Jeff

 

[stinson]

If you have the clipboard open when you copy from the other workbook the
item will make it onto the clipboard, you can then click the item to past
into the workbook with the code.

Not the best way but it will accomplisih what you're lookng for.

Tom