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

 

[Michael M]

You will need to turn off events to prevent the endless loop

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")
Application.EnableEvents = False
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
Application.EnableEvents = True
End Sub

 

[Ironman]

Many thanks Johnny (and you too Michael for your input)

It kind of works. When I clicked J1 I then got a value in J7, which I assumed was a date, and after changing the format it's 08/10/1905. I'm not sure why it's that particular date? I then tested the msgbox by erasing the formula in J1 and made the value greater than J2 and I got the msgbox OK and a "1" in cell I7.

Is it working the way it should?

 

[Ironman]

(Ahh, just seen your comment - should be 2108 representing last month, not a date - understood)

I've just reopened the workbook and it seems to be fine - I guess clicking the cell activated the code for the first time.

Fingers crossed it's looking good - thanks ever so much!

If I want to do the same thing for cells J4 and J5, is it possible to run the same code to include those cells too, with a slightly different message box?

 

[johnnyL]

2108 is the date. 2021 and 08 for the month. August of 2021.

 

[Ironman]

2108 is the date. 2021 and 08 for the month. August of 2021.

Understood. It's now Sep 1 in the UK where I am, so I'm assuming this figure represents the previous month, not the current month?

 

[johnnyL]

It should reflect the date from your operating system. It is not a previous month.

Try changing:

        sCurYearMonth = Format$(Date - Weekday(Date, 2), "yymm")                        ' Set the date variable to a yymm format ex 2108

to:

        sCurYearMonth = Format(Now, "YYMM")                                      ' Set the date variable to a yymm format ex 2109

 

[Ironman]

Ah, that's showing 2109 now, thanks a lot Johnny.

Is it possible you could amend the code so it will also bring up a msgbox when J4 > J5 please?

Thanks again!

 

[johnnyL]

@Ironman, we can't guess what you want. You should explain what J4 and J5 represent, explain what message box that you want displayed if J4 > J5, and is that a once a month occurrence of the message box or every time that J4 > J5? Any other values that should be changed during the check for J4 > J5? Stuff like that.

 

[Ironman]

Apologies Johnny, J4 and J5 contain formulas as below:

Exercise Log.xlsm
	J
4	0
5	96
Training Log

Cell Formulas
Range	Formula
J4	J4	=SUMIF(Last90Dates,">"&$K$2,Last90Miles)
J5	J5	=SUMIF(Last90Dates,">"&$K$3,Last90Miles)-J4

Named Ranges
Name	Refers To	Cells
'Training Log'!Z_CF802DC6_E5EE_4A61_9969_9ED76BC490FF_.wvu.FilterData	='Training Log'!$A$11:$J$7744	J4:J5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4	Expression	=J4>J5	text	YES

Cells with Data Validation
Cell	Allow	Criteria
J4	Whole number	between 999999999999999000 and 9999999999999990000
J5:J6	Whole number	=1234567

When J4 is greater than J5 I need a msgbox

MsgBox "Congratulations!" & vbNewLine & _
      "You've done more exercise than you did last month!   ", vbInformation, "Monthly Exercise"

As with the previous solution, I need the code to appear only once during the current month and then become dormant until next month, when it reactivates when J4 is greater than J5 (The nature of the data means that once J4 is greater than J5 it will never become less again in the current month).

Thanks again.

 

[johnnyL]

Do you have another helper cell available like I7 in the first part of the code?