Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 38

Thread: VBA: Popup message based on scenarios
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    Feb 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Popup message based on scenarios

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

  2. #12
    New Member
    Join Date
    Feb 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Popup message based on scenarios

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

    PointsAbs = Round(Abs(Points) , 2 )

  3. #13
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    In that case

    Code:
    Dim PointsAbs As String
    and amend :
    Code:
    PointsAbs = Round(Abs(Points) , 2 )

    to :

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

  4. #14
    New Member
    Join Date
    Feb 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    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.

  5. #15
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    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

  6. #16
    New Member
    Join Date
    Feb 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    Makes sense - thank you!

  7. #17
    New Member
    Join Date
    Feb 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    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")

  8. #18
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA: Popup message based on scenarios

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

  9. #19
    New Member
    Join Date
    Feb 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    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

  10. #20
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    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 by Yongle; Aug 28th, 2019 at 05:07 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •