从其他应用程序切换到Excel的事件处理程序? [英] Event Handler for switching from other applications to Excel?

问题描述

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

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

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

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

要求是在激活此工作簿时(单击或按Alt键将其禁用)禁用CellDragAndDrop属性，并在此工作簿不活动时重新启用它.

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.

推荐答案

好，我最初认为这是功能区定制的工作.我无法使用功能区来做到这一点(并不是说不可能，但是我看不到任何会影响此功能的commandMSO).

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

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

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

在名为mod_DragDrop的标准模块中使用以下内容:

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

将其放入Workbook_Open事件处理程序中:

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

注意::如果您结束"运行时或在调试时执行任何可能导致状态丢失的操作，则将丢失事件处理程序.始终可以通过调用Workbook_Open过程来恢复它，因此，另外的保护措施可能是也将其添加到ThisWorkbook代码模块中:

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

我已在 Google文档，以防万一，上面提供的代码中有一些错误的错字.

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.

这篇关于从其他应用程序切换到Excel的事件处理程序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！