Small amendment needed to UDF

[Ironman]

Hi

I was kindly given the below code by a member of the board some time ago.

With Worksheets("Daily Tracking")
    uValue = Sheets("Training Log").Range("MlsYTDLessLastYr")
    Select Case uValue
        Case Is < 0
            utext = "less"
        Case Is = 0
            utext = "equal"
        Case Is > 0
            utext = "further"
    End Select
    uValue = Round(uValue, 0)
MsgBox "You have now run " & uValue & " miles " & _
        utext & " than this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
End With

Range("MlsYTDLessLastYr") is as below:

Exercise Log.xlsm
	C	D	E
5	311	472	-161
Training Log

Cell Formulas
Range	Formula
C5	C5	=INDEX('Daily Tracking'!375:375,1,DailyTrackingColumn)
D5	D5	=INDEX('Daily Tracking'!372:372,1,DailyTrackingColumn-1)
E5	E5	=C5-D5

The code functions perfectly but it isn't grammatically correct when last year's mileage exceeds this year's mileage, as the message box shows a "-" value e.g. "You have now run -161 miles less than this time last year". This is because E5 has the custom format +0;-0;0 and I would like to keep it that way for clarity.

I would be grateful for an extra row of code that removes the negative symbol from the msgbox statement if the value in cell E5 is negative.

Thank you!

 

[DanteAmor]

And if you remove it from a general msg and write a message for each one.

Dim uValue As Double, utext As String
    uValue = Sheets("Training Log").Range("MlsYTDLessLastYr").Value
    Select Case uValue
        Case Is < 0
            msgbox "The message when less"
        Case Is = 0
            msgbox "The message when it's the same"
        Case Is > 0
            msgbox "The message when further"
    End Select

 

[Ironman]

Brilliant! That's exactly what I wanted - here it is:

Dim uValue As Double, utext As String
    uValue = Sheets("Training Log").Range("MlsYTDLessLastYr").Value
    uValue = Round(uValue, 0)
    
Select Case uValue
        Case Is < 0
            MsgBox "You have now run " & Abs(uValue) & " miles less than this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
            
        Case Is = 0
            MsgBox "You have now run the same number of miles as this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
            
        Case Is > 0
            MsgBox "You have now run " & Abs(uValue) & " miles more than this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
    End Select

Thank you so much Dante!

 

[DanteAmor]

Glad we could help & thanks for the feedback