VBA: Popup message based on scenarios

Whiox

New Member
Joined
Feb 19, 2019
Messages
33
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
You could use a Select Case scenario for each condition & message....but how will the code be triggered ??
 
Upvote 0
There are other possible outcomes that you omitted
What happens if either or both values = 0
Amend VBA below to cover all eventualities

Code:
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
 
Upvote 0
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"?
 
Upvote 0
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
 
Upvote 0
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
Code:
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/
 
Upvote 0
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

Code:
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
 
Upvote 0
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
Code:
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
Code:
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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 ...
Code:
PointsAbs = Round(Abs(Points) , 2 )

Ranking is probably already an integer. If not
Code:
RankingAbs = Round(Abs(Ranking) , 0 )
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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