|OK, I thought about it and came up with something that seems to work.|
What this code does is use a "flag" in Sheet1!A1 to determine if it should add 2.5 to B22 based on 2 criteria:
1 - Is it the 1st of the month?
2 - Is this the 1st time that the workbook has been opened on the 1st of the month?
Remember, we only want to add 2.5 once, not every time you open the workbook on the 1st of the month.
Note: This code does not use the TODAY function in A61 to determine the Date. It determines the Date by checking the system date directly within VBA.
Let's assume it's the last day of the month and you open the workbook. Sheet1!A1 will be set to "No" by the first instruction. In fact, Sheet1!A1 will be set to "No" every time time you open the workbook as long as it is not the first of the month.
The code will then check both the Day of the month and the contents of Sheet1!A1. If the Day = 1 and Sheet1!A1 = "No", then it must be the first time that the sheet was opened on the 1st of the month. If both of those conditions are TRUE then
Sheet1!A1 will be set to "Done" and it will add 2.5 to B22.
For the rest of the day (on the 1st) Sheet1!A1 will be "Done", so both If's will fail and the code will simply exit. On the 2nd of the month (and every day of that month) Sheet1!A1 will be set to "No", so the the If section that adds 2.5 will be FALSE and the code won't do anything else.
The way to test this code is to set your system date to various dates, including the 1st of a month and see if it does what you want. One key note: If you have anything on your system that monitors the system date, such as a calendar app, it's going to react to the changes you make to the system date, so be aware.
To use the code...
1 - Open your workbook and press Alt-F11 to open the VBA editor.
2 - In the right hand pane look for a module called ThisWorkbook.
3 - Double click ThisWorkbook to open the module
4 - Paste the following code into the pane
5 - Save the workbork as an xlsm or xlsb file so that the macro can run each time you open the workbook.
Private Sub Workbook_Open()
'If it's not the 1st of the Month, set Sheet1!A1 to "No"
If Day(Date) <> 1 Then Sheets(1).Range("A1") = "No"
'If it's the 1st of the Month and it's the first time that the
'workbook has been opened that day, add 2.5 to B22 and
'set Sheet1!A1 to "Done" so that no more additions will be done that day.
If Day(Date) = 1 And Sheets(1).Range("A1") = "No" Then
Sheets(1).Range("B22") = Sheets(1).Range("B22") + 2.5
Sheets(1).Range("A1") = "Done"
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.