Page 1 of 4 123 ... LastLast
Results 1 to 10 of 38

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

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

    Default VBA: Popup message based on scenarios

    Dear MrExcel community

    Challenge
    I would like to create VBA code that results in a popup message after particular calculations have finished (which are part of a existing VBA code). The wording of the message depends on certain cell values. The message will also have to include certain cell values from the spreadsheet.

    Example

    Cells C29 and C31 display the result of a calculation (they are in a sheet called "results").
    There are four possible scenarios that would each result in a different message:
    1. Both cell values are negative.
    2. Both cell values are positive.
    3. C29 is positive; C31 is negative.
    4. C31 is positive; C29 is negative.


    Each message will refer to these cells in one way or another.
    As an example, the message for scenario 1 might say: "congratulations - you gained C29 points and improved your overall ranking by C31."

    Question
    How can I code nested IF scenarios for popup messages that also include certain cell values, please?

    Many thanks

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,886
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    You could use a Select Case scenario for each condition & message....but how will the code be triggered ??
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

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

    Default Re: VBA: Popup message based on scenarios

    There are other possible outcomes that you omitted
    What happens if either or both values = 0
    Amend VBA below to cover all eventualities

    Code:
    Sub Test()
    
    Dim msg As String, Points As Double, Ranking As Double
    
    Points = Sheets("Results").Range("C29").Value
    Ranking = Sheets("Results").Range("C31").Value
    
    Select Case True
        Case Points > 0 And Ranking > 0
            msg = "congratulations - you gained " & Points & " points and improved your overall ranking by " & Ranking
        Case Points < 0 And Ranking < 0
            msg = "message 2 string"
        Case Points > 0 And Ranking < 0
            msg = "message 3 string"
        Case Points < 0 And Ranking > 0
            msg = "message 4 string"
    End Select
    
    MsgBox msg
        
    End Sub

  4. #4
    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 - that works well.

    I forgot to mention that the cell values in my message string need to be absolute values (no negatives). Any negative cell value needs to be converted in a positive one (e.g. -3 turns into 3). How can I do that please?

    Also just out of curiosity: what does the second line in your VBA exactly do (Dim msg As String, Points As Double, Ranking As Double)? The rest is pretty self-explanatory.

    Good point about one or both values being zero - I didn't think of it, and it is certainly an option. How can I have a separate message for these scenarios, please? Do I just say "Case Points = 0 and Ranking =0"?

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

    Default Re: VBA: Popup message based on scenarios

    Good point about one or both values being zero - I didn't think of it, and it is certainly an option. How can I have a separate message for these scenarios, please? Do I just say "Case Points = 0 and Ranking =0"?
    Yes
    - add the line you correctly deduced and immediately below that a line it to build the message string

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

    Default Re: VBA: Popup message based on scenarios

    Also just out of curiosity: what does the second line in your VBA exactly do (Dim msg As String, Points As Double, Ranking As Double)? The rest is pretty self-explanatory.
    This line declares variables used in the code. It specifies the TYPE of value held in each variable
    Code:
    Dim msg As String, Points As Double, Ranking As Double
    Numbers can be type Double, Single, Long, Integer etc ...

    You did not tell us what the 2 cells might contain so I used Double to be safe
    - I knew that should handle anything in those cells

    Here is some bedtime reading for you
    https://trumpexcel.com/vba-data-type...les-constants/

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

    Default Re: VBA: Popup message based on scenarios

    I forgot to mention that the cell values in my message string need to be absolute values (no negatives). Any negative cell value needs to be converted in a positive one (e.g. -3 turns into 3). How can I do that please?
    To convert value to absolute, use VBA function Abs
    Abs(value)

    The tests must continue to use the true values in the cells
    To avoid repeating the formulas in EVERY message string
    - create 2 new variables to hold the absolute values and use those in your strings

    Code:
    Sub Test2()
    
    Dim msg As String, Points As Double, Ranking As Double, PointsAbs As Double, RankingAbs As Double
    
    Points = Sheets("Results").Range("C29").Value
    Ranking = Sheets("Results").Range("C31").Value
    PointsAbs = Abs(Points)
    RankingAbs = Abs(Ranking)
    
    Select Case True
        Case Points > 0 And Ranking > 0
            msg = "congratulations - you gained " & PointsAbs & " points and improved your overall ranking by " & RankingAbs
        Case Points < 0 And Ranking < 0
            msg = "message 2 string"
        Case Points > 0 And Ranking < 0
            msg = "message 3 string"
        Case Points < 0 And Ranking > 0
            msg = "message 4 string"
    End Select
    
    MsgBox msg
        
    End Sub

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

    Default Re: VBA: Popup message based on scenarios

    Something which may (possibly!) avoid you being confused by Select Case when you modify the code

    VBA exits Select Case on the FIRST found match

    The police are never called in example below because the first test in the list of Cases is satisfied whenever X = 10
    Code:
    Select Case True
      Case X = 10 
        MsgBox "call Fire Brigade"
      Case X = 10 And Y = 5
        MsgBox "call Fire Brigade and call Police"
    End Select
    This is the correct sequence in this example
    Code:
    Select Case True
      Case X = 10 And Y = 5
        MsgBox "call Fire Brigade and call Police"
      Case X = 10 
        MsgBox "call Fire Brigade"
    End Select
    So be careful not to accidentally exclude the any of the tests from being tested
    Last edited by Yongle; Jun 19th, 2019 at 10:12 AM.

  9. #9
    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 Yongle - that all makes lots of sense.

    One last question please - how can I control the number of decimals that will get displayed in my message from my absolute values?

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

    Default Re: VBA: Popup message based on scenarios

    I presume Points are consistently displayed to the same number of decimal points in each message string.
    So replace the line which calculates the variable initially. This rounds to 2 places ...
    Code:
    PointsAbs = Round(Abs(Points) , 2 )
    Ranking is probably already an integer. If not
    Code:
    RankingAbs = Round(Abs(Ranking) , 0 )
    Last edited by Yongle; Jun 19th, 2019 at 05:51 PM.

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
  •