VBA: Popup message based on scenarios

Whiox

New Member
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
You could use a Select Case scenario for each condition & message....but how will the code be triggered ??
 

Yongle

Well-known Member
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
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
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
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
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
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
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
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:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top