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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,975
Office Version
2013
Platform
Windows
You could use a Select Case scenario for each condition & message....but how will the code be triggered ??
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,286
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
 

Whiox

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

Well-known Member
Joined
Mar 11, 2015
Messages
4,286
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,286
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/
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,286
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,286
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:

Whiox

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

Well-known Member
Joined
Mar 11, 2015
Messages
4,286
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:

Forum statistics

Threads
1,078,444
Messages
5,340,319
Members
399,366
Latest member
ahmed elsaid

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top