Stop VB from Clearing Clipboard

[eforti]

Hello All,
I have a file with Workbook Activate/Deactivate and Worksheet Activate/Deactivate commands in it. When someone is trying to copy information from or to this workbook the VB automatically clears the clipboard (or at least that's how I understand it), which makes it so they can't paste that data anywhere else. How can I prevent this, but still keep my current commands?

 

[Andrew Poulsom]

Don't run your code unless CutCopyMode = False:

http://www.mrexcel.com/forum/showthread.php?t=458508

 

[Richard Schollar]

I just ran a test using xl2002 and it seems that just having Worksheet_Activate and Deactivate events fire does not clear the clipboard so I deduce from this that it is likely to be something within the code that is triggered by these events actually losing you your clipboard data. So can you post the code behind these events?

EDIT: Beaten to it by Mr P!

 

[eforti]

Thanks for the quick responses.

The codes I'm running in the workbook change calculations from Manual to Automatic and vise versa.

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

Private Sub Workbook_Close()
Application.Calculation = xlCalculationAutomatic
End Sub
Private Sub Workbook_activate()
Application.Calculation = xlCalculationManual
End Sub

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

 

[Richard Schollar]

Yeah that nobbles it!

You would incorporate Andrew's suggestion with:

Private Sub Workbook_activate()
If Application.CutCopyMode = False Then _
   Application.Calculation = xlCalculationManual
End Sub
 
'and:
 
Private Sub Workbook_Deactivate()
If Application.CutCopyMode = False Then _
   Application.Calculation = xlCalculationAutomatic
End Sub

 

[eforti]

Here's my question then,
The reason I've got this code is that the workbook needs to operate with Manual calculations. But when someone switches to another excel filworkbook they don't remember that calculations are set to manual, and it screws with their other files (In the sense that they don't know what's going on, not actually screwing with the file). It seems like with the code only running when copy mode is false I'll still run into the scenario where people leave the file and end up with their others being set to manual calculations. Is it possible to avoide this?

 

[Richard Schollar]

Your code is only suspended if CutCopyMode is True so it won't affect the users every time they switch between files, but I take your point. I'm not overly familiar with CutCopyMode/clipboard settings/operations so it may be that you can place data on the Windows clipboard rather than the office one (or vice versa) and still retain it even after your Activate?Deactivate runs. I will have to step aside to see if someone else can help on this point, however, I'm afraid. Good luck!

 

[eforti]

Would there be a way for the VB to copy whatever is on the clipboard and save it as a value. Then, once the code is completed, have that value loaded back into the clipboard?

 

[Jaafar Tribak]

Setting the Calculation mode seems to inadvertently clear the clipboard . In fact, same happens in many other situations like displaying any Excel UI dialog window such as the Cell Format or Validation windows etc...

Fortunatly there is an easy API function namely OpenClipBoard which can temporarly lock the clipboard and therefore avoid clearing its contents.

This worked for me :

Option Explicit
 
Private Declare Function OpenClipboard Lib "User32" _
(ByVal hwnd As Long) As Long
 
Private Declare Function CloseClipboard Lib "User32" () As Long

Private Sub Workbook_Open()
 
    Call xlCalcMan
 
End Sub
 
Private Sub Workbook_Close()
 
    Call xlCalcAuto
 
End Sub
 
Private Sub Workbook_activate()
 
    Call xlCalcMan
 
End Sub
 
Private Sub Workbook_Deactivate()
 
    Call xlCalcAuto
 
End Sub

Private Sub xlCalcMan()
 
    OpenClipboard 0
    Application.Calculation = xlCalculationManual
    CloseClipboard
 
End Sub
 
Private Sub xlCalcAuto()
 
    OpenClipboard 0
    Application.Calculation = xlCalculationAutomatic
    CloseClipboard
 
End Sub

 

[Richard Schollar]

That is awesome Jaafar