Macro: Check CheckBox Is True, Current Date For Day/Month, Then Sum TextBox & Cell

Good Morning to All (Especially Dave)!!

I am back with a new issue from our local commander. (Yes, he is a pain). Anyway, I have included a sample of what we are looking at. I am trying to allow the Command Button when clicked to go through multiple conditions before making a decision. So, when someone clicks on Command Button 3 the code should look to see if CheckBox1 is true, then it should check today's date, and if it is between a range of days, or even months, then it would add the number in TextBox1 with the amount already in cell H18. This event will happen every time someone clicks on the Command Button.

The end result is to have several sheets (4 total) for each quarter in the fiscal year, and if the dates are within those parameters, the clicking of the command button will update the correct sheet.

My code doesn't do anything right now. I hope this is the correct way to post a new thread Dave. Please let me know. Thanks for all your help!!

Go Army!!

Re: Macro: Check CheckBox Is True, Current Date For Day/Month, Then Sum TextBox & Cell

Quote from daniel.c

Hello.
Change :

Code

If CheckBox1 = True And Today = DateSerial(2008, 5, 19 - 30) Then

to

Code

If CheckBox1 = True And Date = DateSerial(2008, 5, 19 - 30) Then

HTH
Daniel

Display More

Sorry, But that didn't seem to help. The Cell H18 is still not being updated.

Re: Macro: Check CheckBox Is True, Current Date For Day/Month, Then Sum TextBox & Cell

Quote from Ossah

Hi there.

This is the way I would do it. I am using hardcoded date values but you will probably want the user to enter them.

I have also put in a little error checking incase the textbox is empty.
i hope this is what your looking for.

Code

Private Sub CommandButton3_Click()
    Dim tempDate As Date, startDate As Date, endDate As Date
    
    'lets say for eg startDate is 01/01/2008 and end date is
    '01/06/2008
    'note these dates are the range you want to search within
    
    startDate = "01/01/2008"
    endDate = "01/06/2008"
    
    'temp date is today lets say. This will be the day you are testing.
    tempDate = Mid(Now, 1, 10)
    
    If CheckBox1 = True And tempDate >= startDate And tempDate <= endDate Then
        If TextBox1.Text <> "" Then
            [Sheet1!H18] = [Sheet1!H18] + TextBox1.Text
        End If
    End If
End Sub

Display More

Display More

Ok, I see what you are doing. My dates will be permanent with the exception of each year my range will change due to the year will change. The ranges will be each quarter of the fiscal year wihich are like this:

1st Qtr = 1 Oct 07 - 31 Dec 07
2nd Qtr = 1 Jan 08 - 31 Mar 08
3rd Qtr = 1 Apr 08 - 30 Jun 08
4th Qtr = 1 Jul 08 - 30 Sep 08

The code I was using does work if you use only one day, (which, daniel c. shows above, which I am not sure where you are getting April since the 5th month is May), but this apparently doesn't work for a range of days. Your code seems to be the code that I may need to use, but would I use the "Else If" command to move to another set of date ranges? I am starting to get confused.

Re: Macro: Check CheckBox Is True, Current Date For Day/Month, Then Sum TextBox & Cell

Quote from daniel.c

Just to point out that :

Code

DateSerial(2008, 5, 19)

means 5/19/2008
but

Code

DateSerial(2008, 5, 19 - 30)

means 30 days before that date.
Daniel

Display More

Oops, sorry Daniel. Not big on what all this code means, but I think I got it working now. I am including the spreadsheet with the code I used. Let me know what you think, and also to double check my codes I used from Ossah. Thank you too Ossah, you were a big help!!