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

 

[Whiox]

You are a wonderful person - thank you so much for your help!

 

[Whiox]

One more thing, how can I add a thousands separators to my points score, please?

PointsAbs = Round(Abs(Points) , 2 )

 

[Yongle]

In that case

Dim PointsAbs As [COLOR=#ff0000]String[/COLOR]

and amend :

[COLOR=#333333]PointsAbs = Round(Abs(Points) , 2 )

to :[/COLOR]

PointsAbs = Format(Abs(Points), "#,##0.00")

 

[Whiox]

Thanks once again, that works well.

The issue I am having now though is that one of my calculation operations won't work anymore now that these numbers are defined as "String".

SumAbs = PointsAbs +RankAbs

I'd like to display SumAbs in the same formatting with a thousands separator.

 

[Yongle]

You need to do the calculations using number variables
- the numbers are stored in variables Points and Ranking

You need to take care that you do not end up with stupid rounding differences
- the calculations must be consistent
- this is a "maths" logic issue not a VBA issue.

You have already been given all the code you need
- but ensure that you are rounding each element consistently within itself BEFORE aggregating totals

For example, if PointsAbs is rounded to 2 places, and RankingAbs is also rounded to 2 places
SumAbs = the absolute of (Points rounded to 2 places) + the absolute of (Ranking rounded to 2 places)

But that is NOT necessarily the same result as
(AbsPoints + AbsRanking) rounded to 2 places

 

[Whiox]

Makes sense - thank you!

 

[Whiox]

One follow one question, please.

The popup messages work fine for PointsAbs, but only as long as it is 4 digits or less (e.g. 1,234.56).
If it is five digits I get an empty pop up box (e.g. 12,345.67).

Any idea what might cause this & how it can be rectified?

PointsAbs = Format(Abs(Points), "#,##0.00")

 

[Yongle]

Please post the whole sub and I will take a look
thanks

 

[Whiox]

Thank you - please see below.

I've simplified the code a little bit to make it easier to work through. "PointsAbs" from my earlier message refers to "IAbs" in the example below.


Sub Messagetest()


Worksheets("User interface").Activate

'Popup messages---------------------------------------
Dim msg As String, Total As Currency, Interest As Currency, Principal As Currency
Symbol = Sheets("User interface").Range("D6").Text

Dim TotalAbs As String, InterestAbs As String, PrincipalAbs As String
To = Sheets("User interface").Range("C29").Value
In = Sheets("User interface").Range("C30").Value
Pr = Sheets("User interface").Range("C31").Value

TAbs = Format(Abs(To), "#,##0.00")
IAbs = Format(Abs(In), "#,##0.00")
PAbs = Format(Abs(Pr), "#,##0.00")

Dim IAbs As Currency, PAbs As Currency, IPsum As Currency, IPAbs As String
IAbs = Round(Abs(In), 2)
PAbs = Round(Abs(Pr), 2)
IPsum = IAbs + PAbs
IPAbs = Format((IPsum), "##,##0.00")

Select Case True
'Scenario 1 and 2
Case To > 0 And In > 0 And Pr > 0
msg = "This is " & Symbol & TAbs & "..."

'Scenario 3
Case To > 0 And In > 0 And Pr < 0 And IAbs > PAbs
msg = "This is " & Symbol & IPAbs & " ..."


'Scenario 6
Case To > 0 And In < 0 And Pr > 0 And IAbs < PAbs
msg = "A " & Symbol & TAbs & " B " & Symbol & IAbs & "C " & Symbol & PAbs & " D " & Symbol & TAbs & "..."


MsgBox msg
End Sub

 

[Yongle]

I've simplified the code a little bit to make it easier to work through. "PointsAbs" from my earlier message refers to "IAbs" in the example below

The code you provided does not run
I need code that works - otherwise I have to fix it to help you (unnecessaery waste of time) - your actual code is best
If there is anything confidential then mask that (rephrase comments etc) - but make sure the code runs on your data before posting it

Code Tags
Use code tags when posting so that the code look very similar to how it looks in VBA window (which makes it much easier to read). Click on Reply and then click on the # icon (above reply window) and paste your actual code between the code tags which appear automatically.

[CODE tag] paste code here [/CODE tag]