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?

 

[RoryA]

That is very neat! I've always used the MSForms.DataObject to store the clipboard data and then put it back, but that's even simpler!

 

[Richard Schollar]

The DataObject has a few limitations too doesn't it? Something that the clipboard isn't restricted by. I think it's a great solution!

 

[Andrew Poulsom]

Marvellous Jaafar.

 

[eforti]

Jaafar,
That is incredible! Thank you for the solution. I do have one question about your coding. It seems that when I copy a formula it pastes as special and removes the formula. Is there a work around that fits with your coding?

Again, thank you for the help

 

[Jaafar Tribak]

Thank you all for the feedback.

eforti.

You are right, only the values are preserved and not the formulae.

I suspect this could be worked around via subclassing the Excel application but the code would be quite involved and will certainly have a performance hit .

Maybe there is some other way . Anyway i'll give this a shot later on and see if we can overcome this problem.

 

[Andrew Poulsom]

I think that's because Excel uses the clipboard in a proprietary way. When it's preserved the paste is the Worksheet method (like from an external application) rather than the Range method (from within Excel).

 

[Jaafar Tribak]

Ok. Here is a something that worked well for me .It preserves Formulae as well as any other data in the Clipboard including Shapes etc.. Maybe there are some problems that i haven't noticed.

Workbook demo.

Basically the code hooks the hidden office clipboard window whose Class name happens to be called "CLIPBRDWNDCLASS". I found out about it using the WinSpy program. I searched the net to get info on this Class but doesn't seem to be documented so i relied on experimentation and trial/error.

One pleasant surprise is that ,unlike most Office application windows, this particular clipboard window doesn't seem to cause any noticeable problem when subclassed by VBA obviously except the fact that still one cannot work with the VBIDE while the subclass is set.

The following goes in a Standard Module :

Option Explicit
 
'\\Public Declarations...
Public oObjectBeingCutorCopied1 As Object
Public oObjectBeingCutorCopied2 As Object
Public bCutting As Boolean
Public bCopying As Boolean
 
'\\Private Declarations...
Private Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
 
Private Declare Function CallWindowProc Lib "user32" _
Alias "CallWindowProcA" _
(ByVal lpPrevWndFunc As Long, _
ByVal hwnd As Long, _
ByVal Msg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
 
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function OpenClipboard Lib "user32" _
(ByVal hwnd As Long) As Long
 
Private Declare Function CloseClipboard Lib "user32" () As Long
 
Private Declare Function EmptyClipboard Lib "user32" () As Long
 
Private Declare Function GetActiveWindow Lib "user32" () As Long
 
Private Const GWL_WNDPROC As Long = (-4)
Private Const WM_DESTROYCLIPBOARD As Long = &H307
Private lhwnd As Long
Private lPrevProc As Long
Private bSubClassed As Boolean
 

'\\Public Subs....

Public Sub StartCutCopyMonitor()
 
    Call SubClassXL
    
End Sub
 
Public Sub StopCutCopyMonitor()
 
    Call RemoveSubClassXL
    
End Sub
 

'\\Private Subs....

Private Sub SubClassXL()
 
    If Not bSubClassed Then
        lhwnd = FindWindow("CLIPBRDWNDCLASS", vbNullString)
        OpenClipboard 0
        EmptyClipboard
        CloseClipboard
        lPrevProc = SetWindowLong _
        (lhwnd, GWL_WNDPROC, AddressOf WindowProc)
        bSubClassed = True
    End If
 
End Sub
 
Private Sub RemoveSubClassXL()
    
    SetWindowLong lhwnd, GWL_WNDPROC, lPrevProc
    bSubClassed = False
    Set oObjectBeingCutorCopied1 = Nothing
    Set oObjectBeingCutorCopied2 = Nothing
 
End Sub

Private Function WindowProc _
(ByVal hwnd As Long, ByVal uMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long

    On Error Resume Next
    
    Select Case uMsg
    
        Case Is = WM_DESTROYCLIPBOARD
        
        If GetActiveWindow = _
        FindWindow("XLMAIN", Application.Caption) Then
        
            If Application.CutCopyMode = xlCut Then _
            bCutting = True: bCopying = False
            
            If Application.CutCopyMode = xlCopy Then _
            bCopying = True: bCutting = False
            
            If ActiveWorkbook Is ThisWorkbook Then
                Set oObjectBeingCutorCopied1 = Selection
            Else
                Set oObjectBeingCutorCopied2 = Selection
            End If
            
        End If
        
    End Select
    
    WindowProc = CallWindowProc _
    (lPrevProc, hwnd, uMsg, wParam, lParam)
    
End Function

This goes in the Workbook Module :

Option Explicit
 
Private Enum WbEvent
    Activate = 1
    Deactivate = 2
End Enum

Private Sub Workbook_Open()
 
    Call StartCutCopyMonitor
 
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    Call StopCutCopyMonitor
 
End Sub
 
Private Sub Workbook_Activate()
 
    Application.Calculation = xlCalculationManual
    
    Call PreserveClipBoardContents(Activate)
 
End Sub
 
Private Sub Workbook_Deactivate()
 
    Application.Calculation = xlCalculationAutomatic
    
    Call PreserveClipBoardContents(Deactivate)
 
End Sub

Private Sub PreserveClipBoardContents _
(ByVal WBActivate As WbEvent)
 
    On Error Resume Next
    
    If WBActivate = Activate Then
        If bCopying Then
            oObjectBeingCutorCopied2.Copy
        ElseIf bCutting Then
            oObjectBeingCutorCopied2.Cut
        End If
        Set oObjectBeingCutorCopied1 = oObjectBeingCutorCopied2
    Else
        If bCopying Then
            oObjectBeingCutorCopied1.Copy
        ElseIf bCutting Then
            oObjectBeingCutorCopied1.Cut
        End If
        Set oObjectBeingCutorCopied2 = oObjectBeingCutorCopied1
    End If
 
End Sub

Tested on Xl2003 Office/Win XP. I hope the Clipboard Class name "CLIPBRDWNDCLASS" used in the code is the same on other Platforms as well. Otherwise the code won't work.

 

[RoryA]

Doesn't seem to work on 2007 (On WinXP) although as far as I can see it is finding the clipboard window, since lhwnd is not 0.

 

[Jaafar Tribak]

Doesn't seem to work on 2007 (On WinXP) although as far as I can see it is finding the clipboard window, since lhwnd is not 0.

rorya. Are you having another office application open at the same time like Word or another excel instance ?

The reason I am asking is because WinSpy shows that the Clipboard window is owned by the first office application (Process) that was opened.

 

[RoryA]

Yes - I have two versions of Excel open, Outlook, Access and Word too. I will try again later with just XL2007 opened just to test.