Message pop ups based on cells' value

Eraclis

New Member
Joined
Feb 23, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Hello All! This is the first time I joined such a forum and I am really excited!

I am trying to improve a spreadsheet and I would like any assistance on the matter:

I have 2 sheets (sheet 1 and sheet 2). Sheet 2 is where the user inputs data, whereas Sheet 1 serves as the "totals sheet" where the totals are summed up. I am trying to write a code so that when the total in sheet 1 is greater than a value my spreadsheet would warn the user. Although I wrote a code for Sheet 1 which actually works...it only works if I manually add the value in the range in Sheet 1, however, when the value is input in Sheet 2 (which then is presented as a total in Sheet 1) the warning does not pop up...

Any help would be greatly appreciated
Thank you in advance!

Here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("M3:M1827")) Is Nothing Then

If Range("M1829") >= 275000 Then MsgBox "Warning! You have reached or even exceeded the annual authorised limit for Legal Advice expenses.", vbCritical

If Range("M1829") >= 192500 And Range("M1829") < 247500 Then MsgBox "Warning! You have reached the 70% of the annual authorised limit for Legal Advice expenses.", vbInformation

If Range("M1829") >= 247500 And Range("M1829") < 275000 Then MsgBox "Warning! You have reached the 90% of the annual authorised limit for Legal Advice expenses.", vbExclamation

End If

End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,551
Assuming the value in Range("M1829") in Sheet1 is the result of a formula, try:
VBA Code:
Private Sub Worksheet_Calculate()
    If Range("M1829") >= 275000 Then MsgBox "Warning! You have reached or even exceeded the annual authorised limit for Legal Advice expenses.", vbCritical
    If Range("M1829") >= 192500 And Range("M1829") < 247500 Then MsgBox "Warning! You have reached the 70% of the annual authorised limit for Legal Advice expenses.", vbInformation
    If Range("M1829") >= 247500 And Range("M1829") < 275000 Then MsgBox "Warning! You have reached the 90% of the annual authorised limit for Legal Advice expenses.", vbExclamation
End Sub
Place the macro in the code module for Sheet1.
 
Solution

Eraclis

New Member
Joined
Feb 23, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Assuming the value in Range("M1829") in Sheet1 is the result of a formula, try:
VBA Code:
Private Sub Worksheet_Calculate()
    If Range("M1829") >= 275000 Then MsgBox "Warning! You have reached or even exceeded the annual authorised limit for Legal Advice expenses.", vbCritical
    If Range("M1829") >= 192500 And Range("M1829") < 247500 Then MsgBox "Warning! You have reached the 70% of the annual authorised limit for Legal Advice expenses.", vbInformation
    If Range("M1829") >= 247500 And Range("M1829") < 275000 Then MsgBox "Warning! You have reached the 90% of the annual authorised limit for Legal Advice expenses.", vbExclamation
End Sub
Place the macro in the code module for Sheet1.
Was it THAT simple huh?
Thank you soo much!!

Any ideas for a simple code to pop up a message when user inputs a value in Sheet 2 (it should cover a range of cells)
i.e. if cells value > 10000
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,551
What is the range of cells with value > 10000 and what message do you want to pop up?
 

Eraclis

New Member
Joined
Feb 23, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

What is the range of cells with value > 10000 and what message do you want to pop up?
The range is H3:H3000 and the message is "You have reached the Legal Advice Expense authorised amount per case"

Thank you in advance again!
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,551
Place the macro in the code module for Sheet2.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 8 Then Exit Sub
    If Target.Value > 10000 Then
        MsgBox ("You have reached the Legal Advice Expense authorised amount per case.")
    End If
End Sub
 

Eraclis

New Member
Joined
Feb 23, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Place the macro in the code module for Sheet2.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 8 Then Exit Sub
    If Target.Value > 10000 Then
        MsgBox ("You have reached the Legal Advice Expense authorised amount per case.")
    End If
End Sub
Thank you very much for the assistance, the quick responses and your time!
 

Forum statistics

Threads
1,136,274
Messages
5,674,774
Members
419,525
Latest member
helensesc

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
Top