Better way of creating msgbox when value reached

[Ironman]

Hi

I have the below code that opens a msgbox and creates a "1" in H1 when a named cell falls between 2 values

If Range("No_MILES_RUN_SINCE_1981") > 27990 And Range("No_MILES_RUN_SINCE_1981") < 28000 Then
If [H1] = "" Then
MsgBox "You're now approaching 28,000 miles!", vbInformation, "Miles Run Since 16.04.1981"
[H1] = "1"
End If
End If

Cell H1 is then cleared when the workbook closes with this code (which I'm not convinced is as efficient below the 8th line as it could be - suggestions for improvement would be welcomed)

Private Sub workbook_beforeclose(Cancel As Boolean)
Application.DisplayAlerts = True

        Worksheets("Training Log").[G1:Z1] = vbNullString
        Application.EnableEvents = True
        MsgBox "New backup files created in" & vbNewLine & vbNewLine _
        & "E:\BACKUPS\EXERCISE LOG    " & vbNewLine & vbNewLine _
        & "Y:\DOCUMENTS\EXERCISE LOG\EXIT BACKUPS" & vbNewLine & vbNewLine _
        & "Exercise Log will now close", vbInformation, "Master File Overwritten"
 
         ThisWorkbook.Saved = True
         Application.EnableEvents = True
 
        'If Application.Workbooks.Count < 2 Then
        '    ThisWorkbook.Close False
        '    Application.Quit 'close Excel application and this workbook
        'Else
            'no need to save it twice
        '    ThisWorkbook.Close False
        'End If
 
End Select

Application.EnableEvents = True

End Sub

I'm wondering if there's a more efficient way of creating a msgbox when a value is reached without having to generate a digit and then removing it?

Many thanks!

 

[Logit]

If I correctly understand, you are only checking if the "mileage" is somewhere between two numbers ( "If Range("No_MILES_RUN_SINCE_1981") > 27990 And Range("No_MILES_RUN_SINCE_1981") < 28000 Then" ).

What is the purpose of the "1" in H1 ? What am I missing from the code that is posted ?

 

[Ironman]

That's correct, it would seem pretty straightforward to code, which is why I don't understand the significance of the '1' either.

The code was given to me on this board maybe 15 years ago. From memory it may have been because I said I didn't want that msgbox popping up every time I opened the sheet if the value was still within the parameters, but like you said, I don't know what difference the '1' would have made. I know next to nothing about VBA and assumed the experts on here would know better than I do!

 

[Logit]

The following should be all you need :

If Range("No_MILES_RUN_SINCE_1981") >= 27990 And Range("No_MILES_RUN_SINCE_1981") <= 28000 Then MsgBox "You're now approaching 28,000 miles!", vbInformation, "Miles Run Since 16.04.1981" End If

 

[Ironman]

The following should be all you need :

If Range("No_MILES_RUN_SINCE_1981") >= 27990 And Range("No_MILES_RUN_SINCE_1981") <= 28000 Then MsgBox "You're now approaching 28,000 miles!", vbInformation, "Miles Run Since 16.04.1981" End If

 

[Ironman]

Many thanks Logit. I was thinking about this overnight and it came to me why. The code may have originally been given to me as a solution when comparing values in cells e.g. when the value in A2 becomes greater than A3 and it's time-related i.e. A2 is this month's total and A3 is last month's total, so I only want to know this on the day A2 becomes greater and not for the remaining days of the month, so I'd want the code to 'hibernate' until the same thing happens next month. But this would be irrelevant for conditions unrelated to time like the above, plus there must be more code for that to work and I don't see where that is.

I'll make a separate post for this.

Thanks again!

 

[Logit]

You are welcome.