Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date-dependent calculation | Excel Worksheet Functions | |||
Results dependent on date | Excel Discussion (Misc queries) | |||
Dependent Macro/Toggle Buttons | Excel Programming | |||
macro to run a separate macro dependent on value in cell | Excel Programming | |||
how to make macro not dependent on datafile which has date as its part. | Excel Programming |