Date dependent macro - run once only
 

XP Pro / Excel 2007
I need to run a macro once only each month when the workbook is opened for
the first time in the new month. i.e. It should not run again in the same
month.
However, it should not skip a month in the (unlikely) event that the
workbook is not opened at all during that month and is only opened in the
month following......
Can anyone assist with code for this one?

Date dependent macro - run once only
 

Try something like this (barely tested),

Private Sub Workbook_Open()
Dim ymNow As Long, ymLast As Long
Dim nm As Name

ymNow = Year(Date) & Right("0" & Month(Date) + 1, 2)
On Error Resume Next
Set nm = ThisWorkbook.Names("LastCheck")
If Err.Number Then
Set nm = ThisWorkbook.Names.Add("LastCheck", "=" & ymNow)
nm.Visible = False ' hide from user
Else
ymLast = Val(Right(nm, 6))
End If
On Error GoTo 0

If ymLast < ymNow Then
nm.RefersTo = "=" & ymNow ' update
' call DoStuff ' your macro
End If
End Sub

Obvoiously you could store ymNow in a cell somewhere, hidden perhaps, rather
than a Name.

Not quite sure how you want to handle the scenario of a skipped month, but
hopefully you can adapt the above.

Regards,
Peter T

"Wes_A" wrote in message
...
XP Pro / Excel 2007
I need to run a macro once only each month when the workbook is opened for
the first time in the new month. i.e. It should not run again in the same
month.
However, it should not skip a month in the (unlikely) event that the
workbook is not opened at all during that month and is only opened in the
month following......
Can anyone assist with code for this one?

Date dependent macro - run once only
 

The following code goes in ThisWorkbook module. You need either a special
worksheet or a cell in an existing worksheet where you can store the End of
Month Date for the last month that the macro is run. Manually Initialize this
cell with the End of Month date of this month. (I have used a worksheet
called 'Run Log'.)

When the code runs, it increments the date in the cell to the next End of
Month date. Using the Do Loop, if that date is still before the today's date
then the code runs again and then increments the date again to the next End
of Month date and therefore if one month is missed then it will run twice or
for as many times as months missed.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

See notes at end of post for testing method.

Private Sub Workbook_Open()

Dim dateEOM As Date

dateEOM = Date 'Today's date

Do While dateEOM Worksheets("Run Log") _
.Range("A1")

'Insert your code here in lieu of MsgBox
MsgBox "Run for " & _
Worksheets("Run Log").Range("A1")

Range("A1") = WorksheetFunction.EoMonth _
(Worksheets("Run Log").Range("A1"), 1)
Loop

End Sub

I specifically assigned today's date to a variable so that you can test the
code by first inserting the last day of current month in the cell and then
edit the following line to add 99 days to today's date and you will see the
results with the MsgBox.

dateEOM = Date + 99 'Today's date plus 99 for testing.

--
Regards,

OssieMac

Date dependent macro - run once only
 

What I would do is;

1. Setup a last run month cell. It contains the month number of the last
time the RunOnce macro ran.
2. At the beginning of the Workbook_Open macro, check the value in your last
run month cell.
If the value is different from this month's number, then trigger
your RunOnce macro.
3. At the end of your RunOnce macro, update your last run month cell.

"Wes_A" wrote in message
...
XP Pro / Excel 2007
I need to run a macro once only each month when the workbook is opened for
the first time in the new month. i.e. It should not run again in the same
month.
However, it should not skip a month in the (unlikely) event that the
workbook is not opened at all during that month and is only opened in the
month following......
Can anyone assist with code for this one?