Excel 2010 VBA - 用于在应用程序之间切换的事件处理程序？ [英] Excel 2010 VBA - Event Handler for switching between applications?

问题描述

也许我今天早上对Googles不好，但是我在VBA中找到一个事件处理程序，以便在从其他应用程序切换时激活工作簿。我正在使用Excel 2010。

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

  Private Sub Workbook_Activate（）
 MsgBox1
 End Sub 
 
 Private Sub Workbook_WindowActivate（ByVal Wn As Window）
 MsgBox 2
 End Sub 
  

在课程模块中，我尝试过这些： p>

  Public WithEvents appevent As Application 
 Private Sub appevent_ProtectedViewWindowActivate（ByVal Pvw As ProtectedViewWindow）
 MsgBox1
 End Sub 
 
 Private Sub appevent_ProtectedViewWindowOpen（ByVal Pvw As ProtectedViewWindow）
 MsgBox2
 End Sub 
 
 Private Sub appevent_WindowActivate（ByVal Wb As 
 MsgBox3
 End Sub 
 
 Private Sub appevent_WorkbookActivate（ByVal Wb As Workbook）
 MsgBox4
 End Sub 
 
 Private Sub appevent_WorkbookDeactivate（ByVal Wb As Workbook）
 MsgB ox5
 End Sub 
  

最后的结果是禁用CellDragAndDrop属性此工作簿已激活（单击或替代选项卡），并在此工作簿未激活时重新启用此工作簿。可能是一些简单的事情，我失踪了，但是我不知所措。谢谢！

解决方案

OK我以为这是一个Ribbon功能定制的工作。我不能使用功能区（不要说这是不可能的，但我没有看到任何命令MSO会影响这个功能）。

你的类模块就像这样（我没有尝试其他视图说明你已经枚举了）。该模块封装事件类并包含应用程序级事件处理程序。为此，我想您可能只需要 WorkbookActivate 。提交事件的工作簿将决定是否启用/禁用该属性。

  Public WithEvents appevent As Application 
 Dim ret As String 
 Private Sub appevent_WorkbookActivate（ByVal wb As Workbook）
 
调用ToggleDragAndDrop（wb，ret）
'当满足它的预期工作时，注释掉该行
 msgBoxCell drag& drop enabled =& ret 
 End Sub 
  

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

  Option Explicit 
 Public XLEvents As New cEventClass 
 Sub SetEventHandler （）
 
如果XLEvents.appevent是Nothing然后
设置XLEvents.appevent =应用程序
如果
 
结束Sub 
 
 Sub ToggleDragAndDrop（wb As Workbook，Optional ret $）
 
 Application.CellDragAndDrop =（wb.Name<> ThisWorkbook.Name）
 ret = Application.CellDragAndDrop 
结束Sub 
  

将其放在 Workbook_Open 事件处理程序：

  Option Explicit 
 Private Sub Workbook_Open（）
'当工作簿打开时创建事件处理程序
调用mod_DragDrop.SetEventHandler 
调用mod_DragDrop.ToggleDragAndDrop（Me）
 
 End Sub 
  

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

  Private Sub Workbook_SheetActivate（ByVal Sh As Object）
'在状态丢失已经杀死事件处理程序的情况下的额外保护：
'使用一些工作簿级别事件重新实例化事件处理程序
 
调用Workbook_Open 
 End Sub 
  

我已经在我的 Google文件，以防上面提供的代码中有一些错误的错字。

Perhaps I'm just bad at The Googles this morning, but I'm having a heck of a time finding an event handler in VBA for activating 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 end result here 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. Probably something simple I'm missing, but I'm sick of spinning on this. Thanks!

解决方案

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.

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