VBA: Popup message based on scenarios

Some videos you may like

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
Joined
Feb 19, 2019
Messages
33
One more thing, how can I add a thousands separators to my points score, please?

PointsAbs = Round(Abs(Points) , 2 )
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,697
Office Version
365
Platform
Windows
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
Joined
Feb 19, 2019
Messages
33
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
Joined
Mar 11, 2015
Messages
5,697
Office Version
365
Platform
Windows
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
Joined
Feb 19, 2019
Messages
33
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,697
Office Version
365
Platform
Windows
Please post the whole sub and I will take a look
thanks
 

Whiox

New Member
Joined
Feb 19, 2019
Messages
33
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
Joined
Mar 11, 2015
Messages
5,697
Office Version
365
Platform
Windows
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 :confused:
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:

Watch MrExcel Video

Forum statistics

Threads
1,096,250
Messages
5,449,251
Members
405,560
Latest member
Jadax

This Week's Hot Topics

Top