How to automatically run a macro upon opening a workbook (using a clickable prompt)?
Results 1 to 4 of 4

Thread: How to automatically run a macro upon opening a workbook (using a clickable prompt)?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2018
    Location
    Gent, Belgium
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,472
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

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

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

    https://support.office.com/en-us/art...6-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-...-inputbox.html
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Mar 2018
    Location
    Gent, Belgium
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,472
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

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

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •