Pop up message if the value resulted from formula is higher than a certain value

[free2rhime]

Hello,



I have a file were each row coresponds with food choices from the catering firm, for a certain employee , and on the same row in BR3 there is the calculated total ammount based on the food they choose in the cells (D3: BQ3).



I want a pop up message to be displayed if the value calculated in BR3 is greater than 116.



Also i want to replicate this pop up for each row in the table so the range of cells, were the formula with total ammount is , will be B3:B67.



I tried data validation by choosing Allow: Whole number->Greater than->Minimum:116 or Custom -> Formula:BR3>116



Also if this works i whant to protect from editing the cells that calculate the total ammount, if this information is helpful in a vba code.



Thank you.

 

[rollis13]

This what I came up with but with a macro. It has to be pasted in the sheet's module.
It tests all formulas in ranges BR3 + B3:B67 which .Precedents are been updated and shows a popup when one of these formulas is greater than 116.
Have a try and see if you can use it in your project.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("BR3", "B3:B67").Precedents) Is Nothing Then
        If Target.Dependents > 116 Then MsgBox "Cell " & Target.Dependents.Address & " has exceeded it's maximum value."
    End If
End Sub

 

[Joe4]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Pop up message if the value resulted from formula is higher than a certain value - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[free2rhime]

This what I came up with but with a macro. It has to be pasted in the sheet's module.
It tests all formulas in ranges BR3 + B3:B67 which .Precedents are been updated and shows a popup when one of these formulas is greater than 116.
Have a try and see if you can use it in your project.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("BR3", "B3:B67").Precedents) Is Nothing Then
        If Target.Dependents > 116 Then MsgBox "Cell " & Target.Dependents.Address & " has exceeded it's maximum value."
    End If
End Sub

Hello and thank you for your help.

Unfortunately it still doesn't work because it generates " Run time error '13': Type mismatch for this line of code:

I would like to attach the file but i work on a virtualized server machine for work and there are restictions to install software so i can't install XL2BB Addon.

 

[free2rhime]

Hello and thank you for your help.

Unfortunately it still doesn't work because it generates " Run time error '13': Type mismatch for this line of code:

View attachment 57171

I would like to attach the file but i work on a virtualized server machine for work and there are restictions to install software so i can't install XL2BB Addon.

I posted this questions also on

Pop up message if the value resulted from formula is higher than a certain value - OzGrid Free Excel/VBA Help Forum

 

[rollis13]

Sorry, but now that I've seen your file I must say that I'm unable to adapt my previous suggestion to your project.

 

[free2rhime]

Sorry, but now that I've seen your file I must say that I'm unable to adapt my previous suggestion to your project.

It's ok ,thank you for your help.

I've received a solution to my problem on the other forum, for anyone who is interested.

Pop up message if the value resulted from formula is higher than a certain value - OzGrid Free Excel/VBA Help Forum