VBA: Popup message based on scenarios

[Whiox]

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

 

[Whiox]

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

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

 

[Yongle]

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!

 

[Yongle]

DELETED due to formatting errors

 

[Yongle]

The fix to your problem
VBA was having a problem comparing string values and so they have been converted them to type double with

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)

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 [COLOR=#ff0000]Px[/COLOR] > 0  (TYPO ERROR - should be [COLOR=#ff0000]Pr[/COLOR])
    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 [COLOR=#006400]CDbl(IAbs)[/COLOR] > [COLOR=#006400]CDbl(PAbs)[/COLOR]
        msg = "This is " & Symbol & IPAbs & " ..."

'Scenario 6
    Case Tx > 0 And Ix < 0 And Pr > 0 And [COLOR=#006400]CDbl(IAbs)[/COLOR] < [COLOR=#006400]CDbl(PAbs)[/COLOR]
        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

 

[Whiox]

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

 

[Yongle]

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

 

[Whiox]

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.

 

[Yongle]

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

 

[Whiox]

Any clues yet what I have done wrong?

 

[Yongle]

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