PDA

View Full Version : Solved: Checking if macro run again- same month



satyen
03-29-2010, 06:48 AM
Hello, can anyone help?

This is working correctly however if the macro is run today, for the first time, it should NOT come up with the Warning message, at present with this code it does.
The Warning SHOULD come up if run again on the same day or same month of the same year later on.

Sub DateStamp()
Dim rowsT

Sheets("Page").Activate
Range("A2").Select
Selection.Value = Now()
rowsT = Selection.Value

If Format(rowsT, "mmm") >= Format(Date, "mmm") Then
MsgBox "Warnng”
End if

End sub

mbarron
03-29-2010, 10:08 AM
Your current macro places Now() into the cell then checks to see if Now() is greater or equal to Now(). Except for the milliseconds it takes to run the macro, Now() will = Now().

You also check the dates using format(date,"'mmm"). The value returned is the 3 character string of the month. Jan will be less than Feb, Mar, May, Jun, Jul, Sep, Oct and Nov, but greater than Apr, Aug, and Dec.

Try this version:
Sub DateStamp()
Dim rowsT, prev

prev = Sheets("Page").Range("a2")
rowsT = Now()

If Month(rowsT) = Month(prev) And Year(rowsT) = Year(prev) Then
MsgBox "Warnng” "
Else
Sheets("Page").Range("A2") = Now()
End If

End Sub

satyen
03-29-2010, 10:23 AM
thankyou this works. But just thought if they do want to bypass the warning I should allow them to do so. therefore I ammened this line

MsgBox "Warning, this macro has already been run this month, would you like to run again?” ", vbYesNo, "Warning Message!"

Can you help me with the rest of the code from here.

mbarron
03-29-2010, 10:51 AM
The following will check for the date entry in A2. If the cell is blank, it enters the Now() value. If there is data in the cell, it continues as it had previously. It now contains the option to continue if the macro is run in the same month.

Sub DateStamp2()
Dim rowsT, prev, bContinue
Dim keepGoing As Boolean, newDate As Boolean

If Sheets("Page").Range("A2") = "" Then
Sheets("Page").Range("A2") = Now()
newDate = True
Else
prev = Sheets("Page").Range("a2")
rowsT = Now()
End If


If Month(rowsT) = Month(prev) And Year(rowsT) = Year(prev) And newDate = False Then
bContinue = MsgBox("Warning, this macro has already been run this month, would you like to run again?", _
vbYesNo, "Warning Message!")
If bContinue = vbYes Then
keepGoing = True
End If
Else
Sheets("Page").Range("A2") = Now()
keepGoing = True
End If
If keepGoing = False Then Exit Sub

'rest of code


End Sub

satyen
03-29-2010, 11:31 AM
perfect thank you very much for your effort!