Can anyone explain what's happening in this fairly simple code please?

[Ironman]

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!

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

 

[Ironman]

Yes, use H5

 

[johnnyL]

How about:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
    If Not Intersect(Target, Range("J1:J2")) Is Nothing Then                            ' <--- Set this to the range you want
'
        Dim sCurYearMonth As String                                                     '  Establish variable sCurYearMonth as a String
'
        sCurYearMonth = Format(Now, "YYMM")                                             ' Set the date variable to a yymm format ex 2109'
        If sCurYearMonth > Range("J7").Value Then                                       ' If this is a new month then ...
            Range("I7").Value = 0                                                       '   Set Monthly Msgbox Flag I7 = 0 ie. hasn't displayed yet
            Range("J7").Value = sCurYearMonth                                           '   Save the date variable into J7
        End If
'
        If (Range("J1").Value > Range("J2").Value) And (Range("I7").Value = 0) Then     ' If J1 is greater than J2 & Monthly MsgBox Flag hasn't been set Then ...
            Range("I7").Value = 1                                                       '   Set Monthly Msgbox Flag I7 = 1
'
'           Display Message box
            MsgBox "Congratulations!" & vbNewLine & "You've run more miles than you did last month!   ", vbInformation, "Monthly Mileage"
        End If
    End If
'
'
    If Not Intersect(Target, Range("J4:J5")) Is Nothing Then                            ' <--- Set this to the range you want
        sCurYearMonth = Format(Now, "YYMM")                                             ' Set the date variable to a yymm format ex 2109'
        If sCurYearMonth > Range("J7").Value Then                                       ' If this is a new month then ...
            Range("H5").Value = 0                                                       '   Set Monthly Msgbox Flag H5 = 0 ie. hasn't displayed yet
            Range("J7").Value = sCurYearMonth                                           '   Save the date variable into J7
        End If
'
        If (Range("J4").Value > Range("J5").Value) And (Range("H5").Value = 0) Then     ' If J4 is greater than J5 & Monthly MsgBox Flag hasn't been set Then ...
            Range("H5").Value = 1                                                       '   Set Monthly Msgbox Flag H5 = 1
'
'           Display Message box
            MsgBox "Congratulations!" & vbNewLine & "You've done more exercise than you did last month!   ", vbInformation, "Monthly Exercise"
        End If
    End If
End Sub

 

[Ironman]

Many thanks Johnny, I'm just away from my PC right now, be back to reply properly in about 7 hours ?