Event Handler for switching from other applications to Excel?

Question

I want to activate a workbook when switching from other applications. I'm using Excel 2010.

In the ThisWorkbook object, I've tried the following:

Private Sub Workbook_Activate()
    MsgBox "1"
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    MsgBox "2"
End Sub

In a class module, I've tried these:

Public WithEvents appevent As Application
Private Sub appevent_ProtectedViewWindowActivate(ByVal Pvw As ProtectedViewWindow)
    MsgBox "1"
End Sub

Private Sub appevent_ProtectedViewWindowOpen(ByVal Pvw As ProtectedViewWindow)
    MsgBox "2"
End Sub

Private Sub appevent_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    MsgBox "3"
End Sub

Private Sub appevent_WorkbookActivate(ByVal Wb As Workbook)
    MsgBox "4"
End Sub

Private Sub appevent_WorkbookDeactivate(ByVal Wb As Workbook)
    MsgBox "5"
End Sub

The requirement is to disable the CellDragAndDrop property when this workbook is activated (either clicked-on or alt-tabbed-to) and to re-enable it when this workbook is not active.

Answer 1

OK I thought this was a job for Ribbon customization at first. I'm not able to do it with the Ribbon (not to say it is not possible, but I don't see any commandMSO's that would affect this functionality).

Your class module like so (I did not experiment with the other view states that you had enumerated). This module encapsulates the event class and contains the application-level event handlers. For this purpose, I think you might only need the WorkbookActivate. The workbook raising the event will determine whether to enable/disable that property.

Public WithEvents appevent As Application
Dim ret As String
Private Sub appevent_WorkbookActivate(ByVal wb As Workbook)

    Call ToggleDragAndDrop(wb, ret)
    'Comment out this line when satisfied it is working as expected
    MsgBox "Cell drag & drop enabled = " & ret
End Sub

Use the following in a standard module named mod_DragDrop:

Option Explicit
Public XLEvents As New cEventClass
Sub SetEventHandler()

If XLEvents.appevent Is Nothing Then
    Set XLEvents.appevent = Application
End If

End Sub

Sub ToggleDragAndDrop(wb As Workbook, Optional ret$)

    Application.CellDragAndDrop = (wb.Name <> ThisWorkbook.Name)
    ret = Application.CellDragAndDrop
End Sub

Put this in the Workbook_Open event handler:

Option Explicit
Private Sub Workbook_Open()
    'Create the event handler when the workbook opens
    Call mod_DragDrop.SetEventHandler
    Call mod_DragDrop.ToggleDragAndDrop(Me)

End Sub

Note: If you "end" run-time or do anything while debugging which would cause state loss, you will lose the event handler. This can always be restored by calling the Workbook_Open procedure, so an additional safeguard might be to add this also in the ThisWorkbook code module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
' Additional safeguard in case state loss has killed the event handler:
' use some workbook-level events to re-instantiate the event handler

    Call Workbook_Open
End Sub

I have made a copy of my file available on my Google Docs, just in case there is some errant typo in the code provided above.

Answer 2

I guess after four years you won't have this question in mind still, so I just wanted to convert your comment into a complete answer, so that others have the answer a bit easier. The solution also works in Excel 2016.

Private Sub Workbook_Open()
    'MsgBox "Opened and disabled"
    Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
    'MsgBox "Activated and disabled"
    Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
    'MsgBox "Deactivated and enabled"
    Application.CellDragAndDrop = True
End Sub

Private Sub Workbook_Before_Close(Cancel As Boolean)
    'MsgBox "Closed and enabled"
    Application.CellDragAndDrop = True
End Sub

I posted this answer as community wiki, because you deserve the credit actually.

Answer 3

It's in an option under the Advanced Tab in the Options --> "Enable fill handle and cell drag-and-drop".

It's not VBA but does exactly what you want.