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

[omega0010]

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.

[Cerbera]

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.

[omega0010]

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 ?

[Cerbera]

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:

Private Sub Workbook_Open()
Call Alpha
ActiveWorkbook.Close False
End Sub

- the line below is optional - it will close the workbook after completing the task.

ActiveWorkbook.Close False

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.

[omega0010]

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