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

Sam Hamels

New Member
Joined
Mar 20, 2018
Messages
47
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Sam Hamels

New Member
Joined
Mar 20, 2018
Messages
47
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:

Code:
Private Sub Workbook_Open()


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


End Sub

2: Create the following macro in a separate module:

Code:
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,911
I'm glad you figured out something that works for you.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,678
Messages
5,488,223
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top