vba 用于从其他应用程序切换到 Excel 的事件处理程序？

Event Handler for switching from other applications to Excel?

提问by Kevin Pope

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

我想在从其他应用程序切换时激活工作簿。我正在使用 Excel 2010。

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

在 ThisWorkbook 对象中，我尝试了以下操作：

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.

要求是在激活此工作簿时禁用 CellDragAndDrop 属性（单击或 alt-tabbed-to），并在此工作簿未激活时重新启用它。

采纳答案by David Zemens

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).

好的，我一开始以为这是功能区定制的工作。我无法使用 Ribbon 执行此操作（并不是说这是不可能的，但我没有看到任何会影响此功能的 commandMSO）。

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.

你的类模块像这样（我没有试验你列举的其他视图状态）。该模块封装了事件类并包含应用程序级事件处理程序。为此，我认为您可能只需要WorkbookActivate. 引发事件的工作簿将决定是否启用/禁用该属性。

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:

在名为 的标准模块中使用以下内容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_Openevent handler:

把它放在Workbook_Open事件处理程序中：

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 ThisWorkbookcode module:

注意：如果在调试时“结束”运行时或执行任何会导致状态丢失的操作，您将丢失事件处理程序。这总是可以通过调用 Workbook_Open 过程来恢复，因此额外的保护措施可能是在ThisWorkbook代码模块中添加它：

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.

我已经在我的Google Docs上提供了我的文件的副本，以防万一上面提供的代码中有一些错误的拼写错误。

回答by Robin Kramer

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.

我想四年后你不会再有这个问题了，所以我只是想把你的评论转换成一个完整的答案，让别人更容易得到答案。该解决方案也适用于 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.

我将此答案发布为社区维基，因为您实际上值得称赞。

回答by garrett

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.

它不是 VBA，而是完全符合您的要求。