Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 38

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

  1. #21
    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 for your patience & support.

    Have posted the corrected code below between code tags. Sorry for that.

    Please note that the code refers to cells (C29-31) in a worksheet called "user interface".

    Code:
    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, IAbs As String, PAbs As String
    Tx = Sheets("User interface").Range("C29").Value
    Ix = Sheets("User interface").Range("C30").Value
    Pr = Sheets("User interface").Range("C31").Value
    
    
    Tabs = Format(Abs(Tx), "#,##0.00")
    IAbs = Format(Abs(Ix), "#,##0.00")
    PAbs = Format(Abs(Pr), "#,##0.00")
    
    
    Dim IPsum As String, IPAbs As String
    IAbs = Round(Abs(Ix), 2)
    PAbs = Round(Abs(Pr), 2)
    IPsum = IAbs + PAbs
    IPAbs = Format((IPsum), "##,##0.00")
    
    
    Select Case True
    'Scenario 1 and 2
    Case Tx > 0 And Ix > 0 And Px > 0
    msg = "This is " & Symbol & Tabs & "..."
    
    
    'Scenario 3
    Case Tx > 0 And Ix > 0 And Pr < 0 And IAbs > PAbs
    msg = "This is " & Symbol & IPAbs & " ..."
    
    
    'Scenario 6
    Case Tx > 0 And Ix < 0 And Pr > 0 And IAbs < PAbs
    msg = "A " & Symbol & Tabs & " B " & Symbol & IAbs & "C " & Symbol & PAbs & " D " & Symbol & Tabs & "..."
    
    
    End Select
    MsgBox msg
    End Sub

  2. #22
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,913
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    Why are there 2 lines for tx =
    The line referring to C29 is pointless because the value is immediately replaced in the next line


    EDIT
    Ignore above - I and T look the same on my phone!
    Last edited by Yongle; Aug 28th, 2019 at 07:16 AM.

  3. #23
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,913
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    DELETED due to formatting errors
    Last edited by Yongle; Aug 29th, 2019 at 04:25 AM.

  4. #24
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,913
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    The fix to your problem
    VBA was having a problem comparing string values and so they have been converted them to type double with
    Code:
    CDbl(IAbs)
    (Another way to approach this would be to treat all the values as numbers and format each value in the message string itself - let me know if you would prefer that method)


    Error in code

    The error in red is nothing to do with the problem you wanted fixing
    - variable assiged a value is Pr
    - but the code contained Px which is not assigned a value
    - amended to Pr

    (Easily avoided by placing Option Explicit at the top of your module before all subs which FORCES you to declare all variables - typo is highlighted by VBA when macro run)

    Unnecessary lines in code ?
    - see ** A and ** B
    (I think you can remove EITHER A & B OR the other 2 lines - test by commenting out A & B and see if code still works for you etc)

    Code:
    Option Explicit
    
    Sub Messagetest()
    
    'THESE 5 VARIABLES WERE NOT DECLARED
    Dim Tx As Double, Ix As Double, Pr As Double
    Dim Tabs As String, Symbol As String
    
    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, IAbs As String, PAbs As String
    
    Tx = Sheets("User interface").Range("C29").Value
    Ix = Sheets("User interface").Range("C30").Value
    Pr = Sheets("User interface").Range("C31").Value
    
    Tabs = Format(Abs(Tx), "#,##0.00")
    IAbs = Format(Abs(Ix), "#,##0.00")  ' ** A    Delete this ??
    PAbs = Format(Abs(Pr), "#,##0.00") ' ** B    Delete this ??
    
    Dim IPsum As String, IPAbs As String
    IAbs = Round(Abs(Ix), 2)   'makes A redundant
    PAbs = Round(Abs(Pr), 2)  'makes B redundant
    IPsum = IAbs + PAbs
    IPAbs = Format((IPsum), "##,##0.00")
    
    Select Case True
    'Scenario 1 and 2
    
    '   Case Tx > 0 And Ix > 0 And Px > 0  (TYPO ERROR - should be Pr)
        Case Tx > 0 And Ix > 0 And Pr > 0
            msg = "This is " & Symbol & Tabs & "..."
    
    'Scenario 3
        Case Tx > 0 And Ix > 0 And Pr < 0 And CDbl(IAbs) > CDbl(PAbs)
            msg = "This is " & Symbol & IPAbs & " ..."
    
    'Scenario 6
        Case Tx > 0 And Ix < 0 And Pr > 0 And CDbl(IAbs) < CDbl(PAbs)
            msg = "A " & Symbol & Tabs & " B " & Symbol & IAbs & "C " & Symbol & PAbs & " D " & Symbol & Tabs & "..."
    
    End Select
    MsgBox msg
    End Sub

    Error Handling

    Why did you remove the lines declaring some of the variables ? Was it because the code failed ?
    - that could happen if one of the cells contained the wrong type of value

    If code is now failing under scenario 3 and 6 then you need to
    - EITHER prevent the wrong type of value being entered in the cell (eg data validation in cell)
    - OR make the code handle that situation (ie tell VBA what to do if a cell contains text instead of a number etc)

    If you need help achieving that let me know
    Last edited by Yongle; Aug 29th, 2019 at 04:34 AM.

  5. #25
    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 very much for your kind help - it means a lot to me.

    Some variables were not declared because I made an error while working with the code. My apologies & thank you for spotting it.

    If I run the corrected code that you posted above, I am still getting an empty popup message. Does it work for you?

    The values in cells C29, C30, and C31 are numbers that are formatted in the accounting style.
    It seems like if those values are below 9,999.99 then the popup messages work just fine.
    If one of them is greater than that (which adds another digit to the value) then I get an empty popup message.

    Could it be some sort of formatting issue in the code?

    Thanks heaps

  6. #26
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,913
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    Examples of messages I get with values in each cell (D6 value = XXX )

    message = This is XXX 123,456.79...
    C29 123456.789
    C30 987654.321
    C31 121212.345

    message = This is XXX 100.00...
    C29 100
    C30 987654.321
    C31 121212.345

    message = This is XXX 987654.32 121212.34 ...
    C29 100
    C30 987654.321
    C31 -121212.345

    message = A XXX 100.00 B XXX 111111.11C XXX 222222.11 D XXX 100.00...
    C29 100
    C30 -111111.111
    C31 222222.111

    message = A XXX 123,456.79 B XXX 111111.11C XXX 222222.11 D XXX 123,456.79...
    C29 123456.789
    C30 -111111.111
    C31 222222.111

  7. #27
    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

    Yep - I am getting the same results as you.

    When I use the following values, however, the message box displays empty:

    C29: -2645.61379593897
    C30: 8426.61542448785
    C31: -11072.2292204268

    I have a feeling we are getting close to solving this conundrum. Thanks for your persistence & unwavering willingness to help.

  8. #28
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,913
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    Will test range of different values and update thread either later today or tomorrow

  9. #29
    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

    Any clues yet what I have done wrong?

  10. #30
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,913
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    Oops - I forgot about this thread.
    Will post reply when back at my PC tomorrow

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
  •