每当打开任何工作簿时运行VBA宏 [英] Run VBA macro whenever any workbook is opened

问题描述

我创建了一个Excel加载项，尝试在打开此会话期间打开的任何和所有工作簿时运行。它有时工作 - 但不总是，我不知道为什么。

我创建了一个文件， addin.xlam ，在这个文件中，在 ThisWorkbook 中，我有：

 私人XLApp作为CExcelEvents 
 
 Private Sub Workbook_Open（）
设置XLApp =新的CExcelEvents 
 End Sub 
  

然后，我创建了一个基于代码的类模块： http://www.cpearson.com/Excel/AppEvent.aspx

  Private WithEvents App As Application 
 
 Private Sub Class_Initialize（）
设置App =应用程序
 End Sub 
 
私有子App_WorkbookOpen（ByVal Wb As Workbook）
如果不是ActiveWorkbook然后
如果InStr（ActiveWorkbook.Name，新引用）然后
 quoteCheck = MsgBox（你想运行报价生成器？，vbYesNo报价生成器）
如果quoteCheck = vbYes然后
准备
 Else 
 End 
 End If 
 End If 
 End If 
 End Sub 
  

如果我关闭Excel并从Windows资源管理器打开一个文件，这一行命中：

  Private Sub App_WorkbookOpen（ByVal Wb As Workbook）
  

启动代码 - 如果有关的工作簿在其名称中具有新引用，则宏将运行。繁荣。完美的。

但是，在运行ONCE之后，如果我打开另一个工作簿，使用单词new quote，这个private sub不会触发。为什么？

我每次如何触发 我打开任何工作簿？

解决方案

显然打开一个工作簿不会自动使其成为活动的工作簿，至少在此事件处理程序启动时。尝试这样：

  Private Sub App_WorkbookOpen（ByVal Wb As Workbook）
如果不是Wb是没有，然后
如果InStr（Wb.Name，New Quote）然后
 quoteCheck = MsgBox（你想运行报价生成器？，vbYesNoQuote Generator）
如果quoteCheck = vbYes然后
准备
 Else 
结束
结束如果
结束如果
结束如果
结束Sub 
  / pre> 
I've created an Excel add-in that attempts to run upon the opening of any and all workbooks that are opened during this session.  It works sometimes - but not always, and I don't know why.

I created a file, addin.xlam, and in this file, in ThisWorkbook, I have:
Private XLApp As CExcelEvents

Private Sub Workbook_Open()
    Set XLApp = New CExcelEvents
End Sub
I then created a class module based off the code here:  http://www.cpearson.com/Excel/AppEvent.aspx
Private WithEvents App As Application

Private Sub Class_Initialize()
    Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    If Not ActiveWorkbook Is Nothing Then
        If InStr(ActiveWorkbook.Name, "New Quote") Then
            quoteCheck = MsgBox("Do you want to run the Quote Generator?", vbYesNo, "Quote Generator")
            If quoteCheck = vbYes Then
                prepare
            Else
                End
            End If
        End If
    End If
End Sub
If I close out of Excel and open a file from Windows Explorer, this line hits:
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
And starts the code - if the workbook in question has "new quote" in its name, the macro runs.  Boom.  Perfect.

However, after this runs ONCE, if I open another workbook with the words "new quote", this private sub doesn't trigger. Why?

How do I get this to trigger each time I open any workbook?
解决方案
Apparently opening a workbook doesn't automatically make it the active workbook, at least in time for this event handler to fire. Try this:
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    If Not Wb Is Nothing Then
    If InStr(Wb.Name, "New Quote") Then
        quoteCheck = MsgBox("Do you want to run the Quote Generator?", vbYesNo, "Quote Generator")
        If quoteCheck = vbYes Then
            prepare
        Else
            End
        End If
    End If
End If
End Sub


                        
这篇关于每当打开任何工作簿时运行VBA宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！