Date condition to be met before running a macro

dubyayoung

New member
Joined
Oct 5, 2012
Messages
11
Reaction score
0
Points
0
I have a worksheet that is used to collect data throughout the month and year, and I need to reset the month and year start data at the beginning of each new month and year. I have macros for that, but I want to set a condition that will only allow the macros to run if the date is the 1st day of the month, or the 1st day of the fiscal year (July 1 in this case) to ensure an accurate new starting point. I would also like to write an error message that will pop up if the macro is used on any day other than the 1st.

There is a date cell in the worksheet that may be able to be referenced for the formula (cell I1 for reference), but I don't really know. I am using the formula =TODAY() for the date, and having it display as Monday, September 01, 2014.

If getting the July 1 date is too difficult I can just use the monthly condition for the annual reset.

Any thoughts or help is appreciated!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Just add a test to your macro

Code:
If Day(Worksheets("Sheet1").Range("I1").Value) = 1 Then

'your code
End If
 

dubyayoung

New member
Joined
Oct 5, 2012
Messages
11
Reaction score
0
Points
0
Thanks Bob, that got the macro to only run on the 1st!

I used that, and found some other help to get my error message to come up, and everything works great!

Here is what I ended up with:

If Day(Worksheets("Daily Status").Range("I1").Value) = 1 Then

‘my code

'Then for my error message I added (found the help on another site)

Else
MsgBox "You can only update the month or year time on the 1st.", vbOKOnly + vbExclamation, "Entry Error"
Exit Sub

End If

End Sub

This gives me a lovely error message box:

error.jpg

Thanks again!
 

Attachments

  • error.jpg
    error.jpg
    12.7 KB · Views: 13

dubyayoung

New member
Joined
Oct 5, 2012
Messages
11
Reaction score
0
Points
0
For posterity, after Bob showed me how to enter the day, I changed my year time update to read the following:

If Month(Worksheets("Daily Status").Range("I1").Value) = 7 And Day(Worksheets("Daily Status").Range("I1").Value) = 1 Then

Now the year can only be updated on July 1 (which is the start of my fiscal year).

I updated my error message accordingly.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
You could make that a tad more readable and efficient with

Code:
With [COLOR=#333333]Worksheets("Daily Status").Range("I1")
[/COLOR][COLOR=#333333]    If Month([/COLOR][COLOR=#333333].Value) = 7 And Day(.Value) = 1 Then
        'do stuff
    End If
End With[/COLOR]


or a single test

Code:
[COLOR=#333333]If Format([/COLOR][COLOR=#333333]Worksheets("Daily Status").Range("I1").Value, "ddmm") = "0107" [/COLOR][COLOR=#333333]Then[/COLOR]
[COLOR=#333333]    'do stuff[/COLOR]
[COLOR=#333333]End If[/COLOR]
 
Top