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

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. 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!
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:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Thanks for the warning Johnny! What does sCurYearMonth do?
 
Upvote 0
That variable is used to store the current 2 digit year (21) and the current 2 digit month (08) ... yymm ... 2108

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Range("I2").Value <= Range("I3").Value Then                  ' If I2 is less than or equal to I3 Then ...
        Range("I7").Value = 0                                       '   Set I7 = 0
    Else                                                            ' Else
        Dim sCurYearMonth As String                                 '   Establish variable sCurYearMonth as a String
'
        sCurYearMonth = Format$(Date - Weekday(Date, 2), "yymm")    '   Set the variable to a yymm format ex 2108
'
        If sCurYearMonth <> Range("I7").Value Then                  '   If the variable does not = I7 Then ...
            Range("I7").Value = 1                                   '       Set I7 = 1
            Range("J7").Value = sCurYearMonth                       '       Save the variable into J7
'
'           Display Message box
            MsgBox "Congratulations!" & vbNewLine & "You've run more miles than you did last month!   ", vbInformation, "Monthly Mileage"
        End If
    End If
End Sub
 
Upvote 0
Thanks again Johnny.

Will the above work with a 'If I2 > I3' statement, which is what's supposed to trigger the msgbox? (but I don't know to write that) and only trigger it once in the current month?
 
Upvote 0
You will be in an endless loop as it is written because as it is written, any value entered into a cell triggers the code to be reran.
 
Upvote 0
I'm sorry Johnny, your understanding is way greater than mine. I guess you can see what I'm trying to achieve but I don't have the knowledge to amend the code to make it work.
 
Upvote 0
Why don't you start by explaining what I2, I3, I7, & J7 represent. Then explain what you want to happen and when you want it to happen. We can then redo the code to do what you want it to do in regards to the code that you currently provided.
 
Upvote 0
Thanks Johnny.

I7 and J7 are helper cells.

I2 and I3 (it's actually J1 and J2 below) contain formulas as below.

Exercise Log.xlsm
J
138
233
Training Log
Cell Formulas
RangeFormula
J1J1=(SUMIFS(D12:D23358,A12:A23358,"<="&EOMONTH(TODAY(),0),A12:A23358,">"&EOMONTH(TODAY(),-1)))+ExerciseBikeHrsThisMth+WalkingHrsThisMth+OutdoorBikeHrsThisMth
J2J2=(SUMIFS(D12:D23358,A12:A23358,"<="&EOMONTH(TODAY(),-1),A12:A23358,">"&EOMONTH(TODAY(),-2)))+ExerciseBikeHrsLastMth+WalkingHrsLastMth+OutdoorBikeHrsLastMth
Named Ranges
NameRefers ToCells
'Training Log'!All_Log_Dates=OFFSET('Training Log'!$A$12,0,0,'Training Log'!$B$10)J1:J2
'Training Log'!All_Log_Times=OFFSET('Training Log'!$D$12,0,0,'Training Log'!$B$10)J1:J2
Last90Dates=OFFSET(LastLogDate,0,0,-90)J1:J2
Last90Times=OFFSET(Last90Dates,0,3)J1:J2
LastLogDate=OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0)J1:J2
LastRunDate=OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0)J1:J2
Log_LastDate=INDEX('Training Log'!All_Log_Dates,ROWS('Training Log'!All_Log_Dates))J1:J2
LogYear_Log_Dates=INDEX('Training Log'!All_Log_Dates,MATCH(DATE(LogYear-1,12,31),'Training Log'!All_Log_Dates,1)+1):INDEX('Training Log'!All_Log_Dates,ROWS('Training Log'!All_Log_Dates))J1:J2
LogYear_Log_Times=INDEX('Training Log'!All_Log_Times,MATCH(DATE(LogYear-1,12,31),'Training Log'!All_Log_Dates,1)+1):INDEX('Training Log'!All_Log_Times,ROWS('Training Log'!All_Log_Dates))J1:J2
x=OFFSET(LastLogDate,0,0,-90)J1:J2
'Training Log'!YTD_Dates=OFFSET('Training Log'!$A$5491,0,0,366)J1:J2
'Training Log'!YTD_Times=OFFSET('Training Log'!YTD_Dates,0,3)J1:J2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J1Expression=J1>J2textYES
Cells with Data Validation
CellAllowCriteria
J1Whole numberbetween 999999999999999000 and 9999999999999990000
J2Whole numberbetween 999999999999999000 and 9999999999999990000


What I want is this: if the value in I2 exceeds I3 (J1/J2) then a msgbox is triggered with the msg in my first post. I want this msgbox to appear only once for the remainder of the current month and then trigger again next month, again, whenever the value in I2 exceeds I3 and so on, every month.

Thanks again.
 
Last edited:
Upvote 0
Ok, I haven't tested this, but it works in my head. But that is far from foolproof. :rolleyes:

VBA Code:
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$(Date - Weekday(Date, 2), "yymm")                        ' Set the date variable to a yymm format ex 2108
'
        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
End Sub
 
Upvote 0

Forum statistics

Threads
1,215,203
Messages
6,123,627
Members
449,109
Latest member
Sebas8956

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top