VBA: Popup message based on scenarios

W

Whiox

New Member
Joined
Feb 19, 2019
Messages
33
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
 
Sort by date Sort by votes

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
C29: -2645.61379593897
C30: 8426.61542448785
C31: -11072.2292204268
Click to expand...


1. the values of your variables based on those values

Tx -2645.61379593897
Ix 8426.61542448785
Pr-11072.2292204268
Tabs 2,645.61
IAbs 8,426.62
PAbs 11,072.23

2. what is the problem ?

Tx is NEGATIVE but NONE of your cases allow for a negative value for Tx

Case Tx > 0 And Ix > 0 And Pr > 0
Case Tx > 0 And Ix > 0 And Pr < 0 And CDbl(IAbs) > CDbl(PAbs)
Case Tx > 0 And Ix < 0 And Pr > 0 And CDbl(IAbs) < CDbl(PAbs)

3. solution
Case Tx < 0 And Ix > 0 And Pr > 0
etc
the number of cases dependes on how many different messages you require

and what about Tx = 0 ?


4. Perhaps you need to use Case Else as final case for anything that you have not included

Select Case explained with examples: https://trumpexcel.com/vba-select-case/
 
Last edited:
Upvote 0
Thanks again Yongle

I have amended my code.

Keeping input the values the same:
C29= -2645
C30= 8426
C31= -11072

I get the "something is odd here" message.
I would expect to get message "10" instead (Total < 0 And Interest > 0 And Principal < 0 And InterestAbs < PrincipalAbs)

Code: 
Option ExplicitSub Messagetest()
   Worksheets("User interface").Activate
 
'Popup messages---------------------------------------


'Variable definitions======
Dim msg As String, Total As Currency, Interest As Currency, Principal As Currency, Symbol As String
  Symbol = Sheets("User interface").Range("D6").Text
  Total = Sheets("User interface").Range("C29").Value
  Interest = Sheets("User interface").Range("C30").Value
  Principal = Sheets("User interface").Range("C31").Value
   
Dim TotalAbs As String, InterestAbs As String, PrincipalAbs As String
  TotalAbs = Format(Abs(Total), "#,##0.00")
  InterestAbs = Format(Abs(Interest), "#,##0.00")
  PrincipalAbs = Format(Abs(Principal), "#,##0.00")
   
  Dim IAbs As Currency, PAbs As Currency, IPsum As Currency, IPAbs As String
   IAbs = Round(Abs(Interest), 2)
   PAbs = Round(Abs(Principal), 2)
   IPsum = IAbs + PAbs
   IPAbs = Format((IPsum), "##,##0.00")
                   
   'Case scenarios======
Select Case True
    'Scenario 1 and 2
    Case Total > 0 And Interest > 0 And Principal > 0
    msg = "1 and 2"
 
    'Scenario 3
    Case Total > 0 And Interest > 0 And Principal < 0 And InterestAbs > PrincipalAbs
    msg = "3"


    'Scenario 6
    Case Total > 0 And Interest < 0 And Principal > 0 And InterestAbs < PrincipalAbs
        msg = "6"
    
    'Scenario 10
   Case Total < 0 And Interest > 0 And Principal < 0 And InterestAbs < PrincipalAbs
        msg = "10"


   'Scenario 11
   Case Total < 0 And Interest < 0 And Principal > 0 And InterestAbs > PrincipalAbs
   msg = "11 "
   
   'Scenario 13 and 14
   Case Interest = 0
   msg = "13 and 14 "
   
   'Scenario 15
   Case Interest < 0 And Principal = 0
   msg = "15"
   
   'Scenario 16
   Case Interest > 0 And Principal = 0
   msg = "16"
             
    Case Else
    msg = "Something is odd here"


End Select


MsgBox msg
End Sub
 
Upvote 0
It would be cleaner to use 2 types of variables with these rules
- type CURRENCY to hold all values and to use in all calculations and numeric tests
- type STRING to display correctly in the message

Amend all calculations and comparisons to only use type CURRENCY
Use STRING variables in message strings

Code: 
Sub Messagetest()
   Worksheets("User interface").Activate
 
'Popup messages---------------------------------------


'Variable definitions======
    Dim msg As String, Symbol As String

[I][COLOR=#ff0000]'use CURRENCY type for ALL calculations and numeric tests[/COLOR][/I]
    Dim Total As Currency, Interest As Currency, Principal As Currency
    Dim TotalAbs As Currency, InterestAbs As Currency, PrincipalAbs As Currency

[I][COLOR=#006400]'use STRING type for ALL message string values[/COLOR][/I]
    Dim strTotalAbs As String, strInterestAbs As String, strPrincipalAbs As String

[I]'get values (variable type = CURRENCY)[/I]
    Symbol = Sheets("User interface").Range("D6").Text
    Total = Sheets("User interface").Range("C29").Value
    Interest = Sheets("User interface").Range("C30").Value
    Principal = Sheets("User interface").Range("C31").Value

[I]'get absolutes (variable type = CURRENCY)[/I]
    TotalAbs = Abs(Total)
    InterestAbs = Abs(Interest)
    PrincipalAbs = Abs(Principal)

[I]'formatting the value to output in messages (variable type = STRING)[/I]
    strTotalAbs = Format(TotalAbs, "#,##0.00")
    strInterestAbs = Format(InterestAbs, "#,##0.00")
    strPrincipalAbs = Format(PrincipalAbs, "#,##0.00")
    
   'Case scenarios======
    Select Case True
    'Scenario 1 and 2
        Case Total > 0 And Interest > 0 And Principal > 0
            msg = "1 and 2"
 
    'Scenario 3
        Case Total > 0 And Interest > 0 And Principal < 0 And InterestAbs > PrincipalAbs
            msg = "3"

    'Scenario 6
        Case Total > 0 And Interest < 0 And Principal > 0 And InterestAbs < PrincipalAbs
            msg = "6"
    
    'Scenario 10
        Case [COLOR=#ff0000]Total[/COLOR] < 0 And [COLOR=#ff0000]Interest[/COLOR] > 0 And [COLOR=#ff0000]Principal[/COLOR] < 0 And [COLOR=#ff0000]InterestAbs[/COLOR] <[COLOR=#ff0000] PrincipalAbs[/COLOR]
            msg = "10"


   'Scenario 11
        Case Total < 0 And Interest < 0 And Principal > 0 And InterestAbs > PrincipalAbs
            msg = "11 "
   
   'Scenario 13 and 14
        Case Interest = 0
            msg = "13 and 14 "
   
   'Scenario 15
        Case Interest < 0 And Principal = 0
            msg = "15"
   
   'Scenario 16
        Case Interest > 0 And Principal = 0
            msg = "16"
             
        Case Else
            msg = "Something is odd here"

End Select


MsgBox msg
End Sub
 
Upvote 0
Changed the code accordingly.

Now I get a compile error "variable not defined" when I try to run it, highlighting the line with the message in Scenario 1 and 2.

Code: 
Option ExplicitSub Messagetest()
   Worksheets("User interface").Activate
 
'Popup messages---------------------------------------


'Variable definitions======
'use CURRENCY type for ALL calculations and numeric tests
    Dim Total As Currency, Interest As Currency, Principal As Currency
    Dim TotalAbs As Currency, InterestAbs As Currency, PrincipalAbs As Currency, IPsum As Currency


'use STRING type for ALL message string values
    Dim strTotalAbs As String, strInterestAbs As String, strPrincipalAbs As String, symbol As String, strIPAbs As String


'get values (variable type = CURRENCY)
    Total = Sheets("User interface").Range("C29").Value
    Interest = Sheets("User interface").Range("C30").Value
    Principal = Sheets("User interface").Range("C31").Value
    
 'get values (variable type = STRING)
     symbol = Sheets("User interface").Range("D6").Text


'get absolutes (variable type = CURRENCY)
    TotalAbs = Abs(Total)
    InterestAbs = Abs(Interest)
    PrincipalAbs = Abs(Principal)
    IPsum = InterestAbs + PrincipalAbs


'formatting the value to output in messages (variable type = STRING)
    strTotalAbs = Format(TotalAbs, "#,##0.00")
    strInterestAbs = Format(InterestAbs, "#,##0.00")
    strPrincipalAbs = Format(PrincipalAbs, "#,##0.00")
    strIPAbs = Format((IPsum), "##,##0.00")

'Case scenarios======
    Select Case True
    'Scenario 1 and 2
        Case Total > 0 And Interest > 0 And Principal > 0
            msg = "1 and 2"
 
    'Scenario 3
        Case Total > 0 And Interest > 0 And Principal < 0 And InterestAbs > PrincipalAbs
            msg = "3"

    'Scenario 6
        Case Total > 0 And Interest < 0 And Principal > 0 And InterestAbs < PrincipalAbs
            msg = "6"
    
    'Scenario 10
        Case [COLOR=#ff0000]Total[/COLOR] < 0 And [COLOR=#ff0000]Interest[/COLOR] > 0 And [COLOR=#ff0000]Principal[/COLOR] < 0 And [COLOR=#ff0000]InterestAbs[/COLOR] <[COLOR=#ff0000] PrincipalAbs[/COLOR]
            msg = "10"


   'Scenario 11
        Case Total < 0 And Interest < 0 And Principal > 0 And InterestAbs > PrincipalAbs
            msg = "11 "
   
   'Scenario 13 and 14
        Case Interest = 0
            msg = "13 and 14 "
   
   'Scenario 15
        Case Interest < 0 And Principal = 0
            msg = "15"
   
   'Scenario 16
        Case Interest > 0 And Principal = 0
            msg = "16"
             
        Case Else
            msg = "Something is odd here"

End Select


MsgBox msg
End Sub
 
Upvote 0
That is because one or more of the variables used in the code has not been declared.
 
Upvote 0
Thank you Yongle - much appreciated

I am travelling at the moment and will have a closer look at the variable definition when I get back.
 
Upvote 0
Hello Yongle - we forgot to declare " Dim msg As String". All fixed up now and working well.

Thank you so much for your help!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Convert Date Problem
Replies
4
Views
757
Michele317
M
D
  • Question Question
Format number as $M or $K based on cell value - millions / thousands
Replies
1
Views
4K
BigBeachBananas
B
L
  • Solved
VBA Run-time Errors
Replies
3
Views
788
StephenCrump
S
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
356
Jagadeshrao
J
F
  • Solved
Data Validation List - Auto update cell values with a twist
Replies
1
Views
1K
Fritz23
F

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top