A question on Application Events in Excel

Lai Kan Leon 596 Reputation points
2021-09-05T13:12:54.777+00:00

Hello,

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


MY QUESTION:

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.

Thanks
Leon

Office Visual Basic for Applications
Office Visual Basic for Applications
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Visual Basic for Applications: An implementation of Visual Basic that is built into Microsoft products.
1,506 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lai Kan Leon 596 Reputation points
    2021-09-06T05:53:23.157+00:00

    Hello,

    Found the answer
    closing
    Thks
    Leon

    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.