Results 1 to 5 of 5

Thread: Date condition to be met before running a macro

  1. #1

    Question Date condition to be met before running a macro

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Just add a test to your macro

    If Day(Worksheets("Sheet1").Range("I1").Value) = 1 Then
    'your code
    End If

  3. #3
    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)

    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:

    Click image for larger version. 

Name:	error.jpg 
Views:	46 
Size:	24.0 KB 
ID:	2609

    Thanks again!
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	error.jpg 
Views:	6 
Size:	12.7 KB 
ID:	2608  

  4. #4
    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.

  5. #5
    You could make that a tad more readable and efficient with

    With Worksheets("Daily Status").Range("I1")
        If Month(.Value) = 7 And Day(.Value) = 1 Then
            'do stuff
        End If
    End With

    or a single test

    If Format(Worksheets("Daily Status").Range("I1").Value, "ddmm") = "0107" Then
        'do stuff
    End If

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts