# VBA: Popup message based on scenarios

#### Whiox

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

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Whiox

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

PointsAbs = Round(Abs(Points) , 2 )

#### Yongle

##### Well-known Member
In that case

Code:
``Dim PointsAbs As [COLOR=#ff0000]String[/COLOR]``
and amend :
Code:
``[COLOR=#333333]PointsAbs = Round(Abs(Points) , 2 )``
to :[/COLOR]
Code:
``PointsAbs = Format(Abs(Points), "#,##0.00")``

#### Whiox

##### New Member
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

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

##### New Member
Makes sense - thank you!

#### Whiox

##### New Member

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

##### Well-known Member
Please post the whole sub and I will take a look
thanks

#### Whiox

##### New Member
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

##### Well-known Member
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]

Last edited: