vba code for same day each month

[gtd526]

Hello,
Looking for the vba code to execute a macro on the same day every month.
Not sure of the actual date ie 10th, 12th, etc
This will be placed in a Private Macro.


thank you

 

[6StringJazzer]

Why are you specifying that it's a Private macro? A Public Sub in a standard Module might be the best solution.

The idea of doing something if it's a particular day is easy:

If Day(Date) = 10 Then ' 12 , etc. 
   MyMacro
End If

But there are two things we need to know to give a complete solution.

1. Something has to invoke this macro to check to see if today is the 10th of the month. Do you want this to be part of some other macro, have the user click a button, run every time the file opens? If you want this to run even if the file is closed, then you will need to set up a Windows Task scheduler, which is outside the scope of Excel.

2. I assume you only want it to run once. So there has to be a way for it to record that it has already run for that day. Is that built into your data somewhere? If not, we have to design it.

 

[gtd526]

Why are you specifying that it's a Private macro? A Public Sub in a standard Module might be the best solution.

The idea of doing something if it's a particular day is easy:

If Day(Date) = 10 Then ' 12 , etc.
   MyMacro
End If

But there are two things we need to know to give a complete solution.

1. Something has to invoke this macro to check to see if today is the 10th of the month. Do you want this to be part of some other macro, have the user click a button, run every time the file opens? If you want this to run even if the file is closed, then you will need to set up a Windows Task scheduler, which is outside the scope of Excel.

2. I assume you only want it to run once. So there has to be a way for it to record that it has already run for that day. Is that built into your data somewhere? If not, we have to design it.

Im adding this to a Private Sub Workbook_Open() and will only execute the macro on a specific day.
With the date and Private Sub, it will do it automatically. I just needed the code to execute on a specific day for each month.

 

[6StringJazzer]

You can take the code I provided and insert it into Workbook_Open. You can implement MyMacro (whatever you want to call it) as Private in the ThisWorkbook module.

That leaves open #2 about how to make sure it only runs once that day.