VBA: Popup message based on scenarios

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
4,296
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
4,296
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
4,296
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
4,296
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:

Forum statistics

Threads
1,078,504
Messages
5,340,773
Members
399,395
Latest member
KJAC

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top