CODEWITHSUNDEEP
excelvba
LucasSeveryn
LucasSeveryn

Reputation: 6262

Executing an action in excel addin (xlam) when file is opened

I'm building an addin to work with one very very annoying externally-supplied file. On opening that file, it does many annoying things, hiding the ribbon being among them. I'd like to block this behaviour. Yes, it's possible to do it in other ways, but I'd like this to be integrated into the addin - the users interacting with the sheet are very unsophisticated - stuff like pressing shift while opening the file is too complicated, and also we want other macros in the file to run normally.

So, questions:

  1. Is it possible for the xlam addin to know when a file is opened, and then execute some functions
  2. Is it possible for the xlam addin to execute some function every 10 seconds from the time Excel is opened? (less pretty solution)

Upvotes: 1

Views: 1534

Answers (1)

Harvey Ellis
Harvey Ellis

Reputation: 606

It is possible yes. You can do something like the following:

In a new class module called AppEvents place the following code:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "Hello new workbook!"
    'do whatever else you'd rather do here
End Sub

Private Sub Class_Terminate()
    Set App = Nothing
End Sub

If you want to detect new workbooks as well as opening ones that have already been created then you need to add this too:

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    MsgBox "Hello new workbook!"
    'do whatever else you'd rather do here
End Sub

Then in a module, we need to create a module level variable to hold the event handler, and create a function that can set this module variable:

Option Explicit

Dim EventHandler As AppEvents

Sub InitHandler()
    Set EventHandler = New AppEvents
    Set EventHandler.App = Application
End Sub

Finally, we need to add a function to the thisworkbook section of the XLAM addin:

Private Sub Workbook_Open()
    Call InitHandler
End Sub

Explanation

The way this works is that when a workbook is opened in Excel, then so long as the XLAM addin is open too, the workbook_open routine will be called. This initialises the event handler in the public module, and in doing so captures the application calls.

The only reason we have the class is that we need to put variables that have events inside class modules.

I used this short article on capturing opening events to write this. Hope this helps.

Upvotes: 4

Related Questions