CODEWITHSUNDEEP
vbaexcel
Brian Powell
Brian Powell

Reputation: 3411

Run VBA macro whenever any workbook is opened

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?

Upvotes: 0

Views: 2138

Answers (2)

Brian Powell
Brian Powell

Reputation: 3411

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 '/REMOVE THIS LINE <--------------
        End If
    End If
End If
End Sub

I'm not sure why this is - but when I checked "no" (so I didn't continue, as I was just testing this out), I ran End in the class module, and that stopped the Sub from re-firing until I restarted Excel. Removing this End code now allows the Private Sub to fire each time I open a workbook....... weird.

Upvotes: 0

John Coleman
John Coleman

Reputation: 51988

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

Upvotes: 1

Related Questions