Message box linked to cell value, different for positive, negative or same value

[Ironman]

Hi

Sheet 'Training Log' E5 contains the formula "=C5-D5"

When the workbook opens I need a message box to run with a message depending on one of these 3 conditions:

If C5-D5 is negative:
"You have now run (Value in E5) & "miles" less than this time last year"

If C5-D5 is the same:
"You have run the same number of miles as of this time last year"

If C5-D5 is positive:
"You have now run (Value in E5) & "miles more than this time last year"

Many thanks!

 

[Fluff]

How about

Private Sub Workbook_Open()
   Select Case Sheets("Training Log").Range("E5")
      Case Is < 0: MsgBox "You have now run " & .Value & " miles less than this time last year"
      Case 0: MsgBox "You have run the same number of miles as of this time last year"
      Case Else: MsgBox "You have now run " & .Value & " miles more than this time last year"
   End Select
End Sub

 

[Ironman]

Many thanks Fluff!

I got an error message with

MsgBox ...& .Value

"Invalid or unqualified reference"

I amended it to

Select Case Sheets("Training Log").Range("E5")
    Case Is < 0: MsgBox "You have now run " & Range("E5").Value & " miles less than this time last year"
    Case 0: MsgBox "You have run the same number of miles as of this time last year"
    Case Else: MsgBox "You have now run " & Range("E5").Value & " miles more than this time last year"
End Select

And it works, thank you!

I just need the figure rounding up to the nearest whole number.

I've tried this

Select Case Sheets("Training Log").Range("E5")
    Case Is < 0: MsgBox "You have now run " & CLng(Range("E5").Value) & " miles less than this time last year"
    Case 0: MsgBox "You have run the same number of miles as of this time last year"
    Case Else: MsgBox "You have now run " & CLng(Range("E5").Value) & " miles more than this time last year"
End Select

But it's prefixed by a "-", which I don't want, as the text explains that. I just need the value without a positive or negative prefix?

Thanks again!

 

[JGordon11]

Private Sub Workbook_Open()
    With Sheets("Training Log").Range("E5")
        Select Case .Value
            Case Is < 0: MsgBox "You have now run " & -.Value & " fewer miles than this time last year"
            Case 0: MsgBox "You have run the same number of miles as of this time last year"
            Case Else: MsgBox "You have now run " & .Value & " miles more than this time last year"
        End Select
    End With
End Sub

 

[Fluff]

Missed the with statement, try

Private Sub Workbook_Open()
   With Sheets("Training Log").Range("E5")
      Select Case .Value
         Case Is < 0: MsgBox "You have now run " & CLng(Abs(.Value)) & " miles less than this time last year"
         Case 0: MsgBox "You have run the same number of miles as of this time last year"
         Case Else: MsgBox "You have now run " & CLng(.Value) & " miles more than this time last year"
      End Select
   End With
End Sub

 

[Ironman]

Perfect, Fluff!

Thank you so much!

(thanks also JGordon11 for your input)

 

[Fluff]

Glad to help & thanks for the feedback.