A question on Application Events in Excel

Lai Kan Leon 596 Reputation points


I am developing an Excel add-in. It works on any worksheet that you may open.

One of the things that the add-in does is this:

If there is conditional formatting (e.g., Red background), then clicking on any cell will clear all conditional formatting.

I use Application Events to do my add-in because I want my add-in to respond to SheetSelectionChange happening in other workbooks.

My add-in works fine, but there is a small annoyance:

The outside workbook must be opened only if the add-in is already installed in Excel.

If you open your workbook first, and then install the add-in, it will not work. Just close the workbook, and reopen it, and it's be OK.

The reason:
In the ThisWorkbook code of the add-in, I have:

Private Sub Workbook_Open()
Set mApp = Application
End Sub


Must I necessarily OPEN a workbook to trigger Set mApp = Application?

Is there another way to trigger Set mApp = Application, so that my Add-in will work even if the workbook is opened FIRST, and then the Add-in is opened?

I am very new to Application Events, and things are not very clear in my mind.


0 comments No comments
{count} votes

0 additional answers

Sort by: Most helpful