• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pop Up Message, if numbers in the cell doesn't matches some predefined criteria

Hi,

I want to include some Pop Up Message, if numbers in the cell doesn't matches some predefined criteria.

Details are in the attached spreadsheet.

Regards,
Manish
 

Attachments

  • Popup error message.xlsm
    11.6 KB · Views: 7
As per your example this could be a quick solution to be pasted in the sheet's module. Since it triggers by event Worksheet_Change at every change of a cell in range B3:B7 (manual changes) you will see a popup with the response. If this range contains formulas you will need a completely different approach.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim message As String
    If Intersect(Target, Range("B3:B7")) Is Nothing Then Exit Sub
    If Range("B3") < Range("B5") Then message = message & "- ROIC should be higher than WACC" & vbLf
    If Range("B4") < Range("B7") Then message = message & "- Perpetual Growth should be higher than Terminal Growth" & vbLf
    If Range("B5") > Range("B4") Then message = message & "- WACC should be lower than ROIC" & vbLf
    If Range("B7") > Range("B4") Then message = message & "- Terminal Growth should be higher than Perpetual Growth"
    MsgBox message
End Sub
 
As per your example this could be a quick solution to be pasted in the sheet's module. Since it triggers by event Worksheet_Change at every change of a cell in range B3:B7 (manual changes) you will see a popup with the response. If this range contains formulas you will need a completely different approach.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim message As String
    If Intersect(Target, Range("B3:B7")) Is Nothing Then Exit Sub
    If Range("B3") < Range("B5") Then message = message & "- ROIC should be higher than WACC" & vbLf
    If Range("B4") < Range("B7") Then message = message & "- Perpetual Growth should be higher than Terminal Growth" & vbLf
    If Range("B5") > Range("B4") Then message = message & "- WACC should be lower than ROIC" & vbLf
    If Range("B7") > Range("B4") Then message = message & "- Terminal Growth should be higher than Perpetual Growth"
    MsgBox message
End Sub
Thanks Rollis, this is working fine.. but what should be done if the range contains formula. And there should be no Message Box, when all the conditions are matching.
 
To avoid the empty MsgBox change:
Code:
MsgBox message
to:
Code:
If message <> "" Then MsgBox message
A very 'quick' solution to detect changes in formulas is to use, instead of the event Worksheet_Change, this other event macro but as it is it will trigger at every calculation in the entire sheet.
Code:
Option Explicit
Private Sub Worksheet_Calculate()
    Dim message As String
    If Range("B3") < Range("B5") Then message = message & "- ROIC should be higher than WACC" & vbLf
    If Range("B4") < Range("B7") Then message = message & "- Perpetual Growth should be higher than Terminal Growth" & vbLf
    If Range("B5") > Range("B4") Then message = message & "- WACC should be lower than ROIC" & vbLf
    If Range("B7") > Range("B4") Then message = message & "- Terminal Growth should be higher than Perpetual Growth"
    If message <> "" Then MsgBox message
End Sub
 
Back
Top