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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,402
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
5,267
Office Version
365
Platform
Windows
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
5,267
Office Version
365
Platform
Windows
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
5,267
Office Version
365
Platform
Windows
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
5,267
Office Version
365
Platform
Windows
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
5,267
Office Version
365
Platform
Windows
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
5,267
Office Version
365
Platform
Windows
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,089,607
Messages
5,409,259
Members
403,257
Latest member
Larry Light

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top