Event handler to switch from other applications to Excel?

Question

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

In the ThisWorkbook object, I tried the following:

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

In the class module, I tried this:

 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 

You need to disable the CellDragAndDrop property when this workbook is activated (either pressed or alt-tabbed-to), and re-enable it when this workbook is inactive.

Answer 1

OK. I thought it was the job of setting up the tape at the beginning. I cannot do this with the tape (not to say that this is impossible, but I do not see any MSO commands that will affect this functionality).

An example of your class module (I have not experimented with other view states that you have listed). This module encapsulates an event class and contains application-level event handlers. For this purpose, I think you might only need a WorkbookActivate . The book raising the event will determine whether to enable or disable this 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 

In a standard module named mod_DragDrop use the following:

 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 “finish” the runtime or do something during debugging that will result in a loss of state, you will lose an event handler. You can always restore this by calling the Workbook_Open procedure, so adding this to the ThisWorkbook code module can also be extra protection:

 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 made a copy of my file on my Google Docs , just in case there is some error in the above code.

Answer 2

This is in the option on the Advanced tab in Options → Enable cell descriptor and cell drag.

This is not VBA, but it does exactly what you want.

Answer 3

I think that after four years you still will not have this question, so I just wanted to convert your comment into a complete answer so that it would be easier for others. 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 on the community wiki because you really deserve praise.