Trigger my macro code when user enables macros in

Question

As macros are disabled by default (enabling them by default is not an option) whenever I open my excel worksheet containing macros I get a popup asking if I want to enable macros. Is it possible to catch the 'OK' button press event of this popup and run a macro.

I.e. if the user chooses to enable macros then my macro should run in response to that.

Answer 1

Firstly and regarding your "enabling them by default is not an option" statement, bear in mind that this is not necessarily true. The user can allow all the macros to be automatically executed without any prompt (under the Developer tab, Macro security, select "Enable all macros (not recommended...)"). In case of not having the macros enabled by default, you cannot do anything before the user allows the given macro to run. Once the macro is running, you have various events being automatically triggered right at the start, where you can put your code. For example: in the file ThisWorkbook (under "Microsoft Excel Objects"), you can use the Workbook_Open method. Sample code:

Private Sub Workbook_Open()
    MsgBox "This is the soonest I can popup"
End Sub

If you copy this code in the aforementioned file, you would see a popup appearing right after the macros has been enabled.

NOTE: the fact of having its own file type (.xlsm) should be indicative enough. Unfortunately, quite a few people think that macros have very limited functionalities (mainly restricted to the given spreadsheet), what is a dangerous misconcepcion: a macro can do virtually the same things than a conventional .exe file.