VBA: Popup message based on scenarios

[Whiox]

Dear MrExcel community

Challenge
I would like to create VBA code that results in a popup message after particular calculations have finished (which are part of a existing VBA code). The wording of the message depends on certain cell values. The message will also have to include certain cell values from the spreadsheet.

Example

Cells C29 and C31 display the result of a calculation (they are in a sheet called "results").
There are four possible scenarios that would each result in a different message:

1. Both cell values are negative.
2. Both cell values are positive.
3. C29 is positive; C31 is negative.
4. C31 is positive; C29 is negative.

Each message will refer to these cells in one way or another.
As an example, the message for scenario 1 might say: "congratulations - you gained C29 points and improved your overall ranking by C31."

Question
How can I code nested IF scenarios for popup messages that also include certain cell values, please?

Many thanks

 

[Michael M]

You could use a Select Case scenario for each condition & message....but how will the code be triggered ??

 

[Yongle]

There are other possible outcomes that you omitted
What happens if either or both values = 0
Amend VBA below to cover all eventualities

Sub Test()

Dim msg As String, Points As Double, Ranking As Double

Points = Sheets("Results").Range("C29").Value
Ranking = Sheets("Results").Range("C31").Value

Select Case True
    Case Points > 0 And Ranking > 0
        msg = "congratulations - you gained " & Points & " points and improved your overall ranking by " & Ranking
    Case Points < 0 And Ranking < 0
        msg = "message 2 string"
    Case Points > 0 And Ranking < 0
        msg = "message 3 string"
    Case Points < 0 And Ranking > 0
        msg = "message 4 string"
End Select

MsgBox msg
    
End Sub

 

[Whiox]

Thank you very much - that works well.

I forgot to mention that the cell values in my message string need to be absolute values (no negatives). Any negative cell value needs to be converted in a positive one (e.g. -3 turns into 3). How can I do that please?

Also just out of curiosity: what does the second line in your VBA exactly do (Dim msg As String, Points As Double, Ranking As Double)? The rest is pretty self-explanatory.

Good point about one or both values being zero - I didn't think of it, and it is certainly an option. How can I have a separate message for these scenarios, please? Do I just say "Case Points = 0 and Ranking =0"?

 

[Yongle]

Good point about one or both values being zero - I didn't think of it, and it is certainly an option. How can I have a separate message for these scenarios, please? Do I just say "Case Points = 0 and Ranking =0"?

Yes
- add the line you correctly deduced and immediately below that a line it to build the message string

 

[Yongle]

Also just out of curiosity: what does the second line in your VBA exactly do (Dim msg As String, Points As Double, Ranking As Double)? The rest is pretty self-explanatory.

This line declares variables used in the code. It specifies the TYPE of value held in each variable

Dim msg As String, Points As Double, Ranking As Double

Numbers can be type Double, Single, Long, Integer etc ...

You did not tell us what the 2 cells might contain so I used Double to be safe
- I knew that should handle anything in those cells

Here is some bedtime reading for you
https://trumpexcel.com/vba-data-types-variables-constants/

 

[Yongle]

I forgot to mention that the cell values in my message string need to be absolute values (no negatives). Any negative cell value needs to be converted in a positive one (e.g. -3 turns into 3). How can I do that please?

To convert value to absolute, use VBA function Abs
Abs(value)

The tests must continue to use the true values in the cells
To avoid repeating the formulas in EVERY message string
- create 2 new variables to hold the absolute values and use those in your strings

Sub Test2()

Dim msg As String, Points As Double, Ranking As Double, [COLOR=#ff0000]PointsAbs[/COLOR] As Double, [COLOR=#ff0000]RankingAbs[/COLOR] As Double

Points = Sheets("Results").Range("C29").Value
Ranking = Sheets("Results").Range("C31").Value
[COLOR=#ff0000]PointsAbs[/COLOR] = Abs(Points)
[COLOR=#ff0000]RankingAbs[/COLOR] = Abs(Ranking)

Select Case True
    Case Points > 0 And Ranking > 0
        msg = "congratulations - you gained " & [COLOR=#ff0000]PointsAbs[/COLOR] & " points and improved your overall ranking by " & [COLOR=#ff0000]RankingAbs[/COLOR]
    Case Points < 0 And Ranking < 0
        msg = "message 2 string"
    Case Points > 0 And Ranking < 0
        msg = "message 3 string"
    Case Points < 0 And Ranking > 0
        msg = "message 4 string"
End Select

MsgBox msg
    
End Sub

 

[Yongle]

Something which may (possibly!) avoid you being confused by Select Case when you modify the code

VBA exits Select Case on the FIRST found match

The police are never called in example below because the first test in the list of Cases is satisfied whenever X = 10

Select Case True
  Case X = 10 
    MsgBox "call Fire Brigade"
  Case X = 10 And Y = 5
    MsgBox "call Fire Brigade and call Police"
End Select

This is the correct sequence in this example

Select Case True
  Case X = 10 And Y = 5
    MsgBox "call Fire Brigade and call Police"
  Case X = 10 
    MsgBox "call Fire Brigade"
End Select

So be careful not to accidentally exclude the any of the tests from being tested

 

[Whiox]

Thank you Yongle - that all makes lots of sense.

One last question please - how can I control the number of decimals that will get displayed in my message from my absolute values?

 

[Yongle]

I presume Points are consistently displayed to the same number of decimal points in each message string.
So replace the line which calculates the variable initially. This rounds to 2 places ...

PointsAbs = Round(Abs(Points) , 2 )

Ranking is probably already an integer. If not

RankingAbs = Round(Abs(Ranking) , 0 )