VBA: Popup message based on scenarios

Whiox

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,239
Why are there 2 lines for tx = :confused:
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!:LOL:
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,239
DELETED due to formatting errors
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,239
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 [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
 
Last edited:

Whiox

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

Well-known Member
Joined
Mar 11, 2015
Messages
4,239
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

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

Well-known Member
Joined
Mar 11, 2015
Messages
4,239
Will test range of different values and update thread either later today or tomorrow
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,239
:oops::oops: Oops - I forgot about this thread.
Will post reply when back at my PC tomorrow
 

Forum statistics

Threads
1,077,796
Messages
5,336,379
Members
399,079
Latest member
DeoW

Some videos you may like

This Week's Hot Topics

Top