+ Reply to Thread
Results 1 to 4 of 4

How can we run a Macro called Alpha from a module every day @ 9:00 AM only once ?

  1. #1
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    305

    How can we run a Macro called Alpha from a module every day @ 9:00 AM only once ?

    How can we run a Macro called Alpha from a module every day @ 9:00 AM only once ? there are many other macros in the sheet running under Private Sub Worksheet_Calculate in the main sheet called sheet2.

  2. #2
    Forum Contributor Cerbera's Avatar
    Join Date
    07-06-2010
    Location
    Rotorua, New Zealand
    MS-Off Ver
    Excel 2007, 2013, 2016 & 365
    Posts
    137

    Re: How can we run a Macro called Alpha from a module every day @ 9:00 AM only once ?

    I'd think you have a couple of options on this one.

    The easiest if your pc will be on all of the time is to schedule it outside of excel.

    Simply set the macro to run on opening of the workbook
    (Select workbook in the VBA screen and then Select "workbook" from the drop down at the top (default reads as "(General)") and then on the "(Declarations)" drop down select "Open". Then call the macro that you're after (or just add it in there))

    Then set up a scheduled task to open the workbook at 9am everyday. (search for "Scheduled Tasks" in control panel if on Windows).

    If this is not an option you could look to utilise the VBA OnTime function. Some notes on this are below - I could elaborate on it if the scheduled task method is not an option.

    Schedules a procedure to be run at a specified time in the future (either at a specific time of day or after a specific amount of time has passed).

    expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

    expression Required. An expression that returns an Application object.

    EarliestTime Required Variant. The time when you want this procedure to be run.

    Procedure Required String. The name of the procedure to be run.

    LatestTime Optional Variant. The latest time at which the procedure can be run. For example, if LatestTime is set to EarliestTime + 30 and Microsoft Excel is not in Ready, Copy, Cut, or Find mode at EarliestTime because another procedure is running, Microsoft Excel will wait 30 seconds for the first procedure to complete. If Microsoft Excel is not in Ready mode within 30 seconds, the procedure won’t be run. If this argument is omitted, Microsoft Excel will wait until the procedure can be run.

    Schedule Optional Variant. True to schedule a new OnTime procedure. False to clear a previously set procedure. The default value is True.

  3. #3
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    305

    Re: How can we run a Macro called Alpha from a module every day @ 9:00 AM only once ?

    I appreciate the time that you have given and the knowledge you have parted, But, unfortunately my knowledge is not as sophisticated as yours.
    Further I open my computer at 855 AM every day and would not like the macro called Alpha to run until it is 900AM. ( what happens is that I forget to manually trigger it ! ). Is there a simpler version of all that you have mentioned ?( which includes the name Alpha somewhere so that I can copy and paste it as per your direction ?

  4. #4
    Forum Contributor Cerbera's Avatar
    Join Date
    07-06-2010
    Location
    Rotorua, New Zealand
    MS-Off Ver
    Excel 2007, 2013, 2016 & 365
    Posts
    137

    Re: How can we run a Macro called Alpha from a module every day @ 9:00 AM only once ?

    I can't think of any. The scheduled task one is a great way to go so long as your pc is on at 9am it will be fine.

    The macro you need in your spreadsheet is as follows:

    Press Alt+F11 to launch the visual basic editor in your spreadsheet.

    Then doubleclick on "Thisworkbook" see image:

    This Workbook.png

    In the pane to the right select "Workbook" from the "General" drop down:

    Call Alpha.png

    Type in the macro as per the image:

    Please Login or Register  to view this content.
    - the line below is optional - it will close the workbook after completing the task.

    Please Login or Register  to view this content.
    Save the workbook. *If you want to open the workbook without firing the macro then hold down shift when you open it (works most of the time!)

    Now to set the scheduled task click on the Windows Start button and type "task scheduler" in the search bar.

    Then select the "Task Scheduler" program.

    Now follow these instructions when it opens:

    On the right click on "Create Basic Task...".
    Give the task a name and click next.
    Select a trigger (Daily in the case you mentioned here) and click next. Set the time (9am) and recurrence period and click next.
    In the Action, select "Start a program" and click next. In the Program/script text box browse for your Excel file and click next (leave the other text boxes empty).
    Click on Finish.

    I just tried this on my PC now, and it works.

  5. #5
    Forum Contributor
    Join Date
    12-11-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    305

    Re: How can we run a Macro called Alpha from a module every day @ 9:00 AM only once ?

    Thanks a lot . Will try. I appreciate your help !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] After renaming module, macros can no longer be called using application.run
    By tblasko in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-22-2017, 08:37 PM
  2. [SOLVED] How to reference the name of the module in which a function is called in a worksheet
    By candy.chiu.ad in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-30-2012, 01:28 PM
  3. [SOLVED] Function in Module called in Userform?
    By RGrunden in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2012, 11:19 PM
  4. code called from module check first data only not all of them
    By suny100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-22-2011, 05:20 AM
  5. Macro to delete alpha and alpha numeric values
    By vijanand1279 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-20-2011, 09:13 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1