Ironman
Well-known Member
- Joined
- Jan 31, 2004
- Messages
- 1,069
- Office Version
- 365
- Platform
- Windows
Hi
I'm not good with VBA and I'm having difficulty understanding what the "sCurYearMonth" in the below code (supplied to me on this board many years ago) is doing. The below code has been commented out for years as I didn't need it, but now I do.
I want the msgbox to be triggered only once in the current month, when the value in I2 exceeds the value in I3 (both contain formulas), and then no more msgs until it's triggered again next month.
Does the below code do this? Many thanks!
I'm not good with VBA and I'm having difficulty understanding what the "sCurYearMonth" in the below code (supplied to me on this board many years ago) is doing. The below code has been commented out for years as I didn't need it, but now I do.
I want the msgbox to be triggered only once in the current month, when the value in I2 exceeds the value in I3 (both contain formulas), and then no more msgs until it's triggered again next month.
Does the below code do this? Many thanks!
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("I2").Value <= Range("I3").Value Then
Range("I7").Value = 0
Else
Dim sCurYearMonth As String
sCurYearMonth = Format$(Date - WeekDay(Date, 2), "yymm")
If sCurYearMonth <> Range("I7").Value Then
Range("I7").Value = 1
Range("J7").Value = sCurYearMonth
MsgBox "Congratulations!" & vbNewLine & _
"You've run more miles than you did last month! ", vbInformation, "Monthly Mileage"
End If
End If
End Sub
Last edited: