CODEWITHSUNDEEP
vbaexcel
Gregory
Gregory

Reputation: 315

Workbook_Open handler not firing in add-in when opening workbooks

I have an add-in for Excel that I attempting to use to insert code to automatically rename the workbook if certain criteria are met. When exporting from our repo, all workbooks have the same name which results in 'Excel cannot open two workbooks with the same name at the same time' error happening over and over again. I've gotten the code to work when the user clicks a button in the add-in, but my attempts to do the same by placing the code in the 'ThisWorkbook' section of the addin does not seem to work. Is there a way to do this via the add-in?

Please find my code below.

Private Sub Workbook_Open()
  Dim wbPath As Variant, wbNewname As Variant, newPath As Variant, wbname As Variant
  Dim fileExtension As Variant

  If ActiveWorkbook.Name Like "SR.*" Or ActiveWorkbook.Name Like "SR (#).*" And Application.ActiveWorkbook.Path Like "*Content.IE5*" Then

    Excel.Application.DisplayAlerts = False

    wbname = ActiveWorkbook.Name

    fileExtension = Right(wbname, Len(wbname) - InStrRev(wbname, ".") + 1)

    wbPath = Application.ActiveWorkbook.Path

    wbNewname = "SR" & Int((465480 - 1 + 1) * Rnd + 838588)

    newPath = wbPath & "\" & wbNewname & fileExtension

    ActiveWorkbook.SaveAs Filename:=newPath

    Excel.Application.DisplayAlerts = True

    Set wbPath = Nothing
    Set wbNewname = Nothing
    Set newPath = Nothing
    Set wbname = Nothing
    Set fileExtension = Nothing

  End If

End Sub

Upvotes: 1

Views: 855

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

ThisWorkbook is your add-in - its Open event fires when the add-in file gets opened, not when any workbook is opened beyond that point.

What you want is to handle application-level events, more specifically the WorkbookOpen event. Add a private WithEvents field to your ThisWorkbook module:

Private WithEvents App As Excel.Application

In the Workbook_Open handler (i.e. at startup), Set that object reference:

Private Sub Workbook_Open()
    Set App = Application
End Sub

Now, look near the top of the code pane - there are two dropdowns. The left-hand dropdown is listing all available event providers and interfaces (i.e. Workbook since ThisWorkbook is a Workbook object, but also any WithEvents fields and Implements interfaces you've defined in that module); select your App field.

Then, the right-hand dropdown will list all available events you can handle for App. Pick the WorkbookOpen event, and the VBE will automatically create a procedure stub with the correct signature for you:

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

End Sub

In that handler procedure, you can put the code you want to run whenever a workbook is opened in Excel.

Upvotes: 4

Related Questions