How to automatically run a macro upon opening a workbook (using a clickable prompt)?

[Sam Hamels]

Hi All,

I would like a macro to start running automatically as soon as I open the Excel file.
However, a prompt would ideally appear first, which asks if the user wants to cancel the automatic start of the macro.
The user has the option to click on the prompt (or hit a specific key or something), to cancel the automatic start of the macro.
If the user does not interact with the prompt (lets say he is given a few seconds to do so), the prompt simply disappears and the macro automatically starts running.

I've tried messing around with some things I found by Googling but can't get anything to work.


Thanks in advance for any tips or help!

Cheers,
Sam

 

[Eric W]

As far as starting a macro, you can use the Workbook_Open event. See here:

https://support.office.com/en-us/ar...workbook-1e55959b-e077-4c88-a696-c3017600db44

And as far as the timed prompt, one of our longtime contributors came up with a clever way to do it, see here:

https://www.mrexcel.com/forum/excel-questions/1035997-timed-inputbox.html

 

[Sam Hamels]

Hi Eric,


Thanks for the tips.
After looking into the times prompt, I figured out that a much simpler method was OK for my needs, namely a simple combination of the following two things:

1: setting up the following Workbook_Open event:

Private Sub Workbook_Open()


Application.OnTime Now + TimeValue("00:00:5"), "Macro1"


End Sub

2: Create the following macro in a separate module:

Sub Macro1()


If Range("A1").Value = 1 Then MsgBox "A1 = 1"


End Sub

I now have 5 seconds to change the value in cell A1 to something else than 1, to avoid "the macro" from running automatically.
By "the macro", I mean the MsgBox in my Example, which will be a much more complicated macro in the actual application.


Cheers,
Sam

 

[Eric W]

I'm glad you figured out something that works for you.