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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
Solution
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
 
Upvote 0
What is the range of cells with value > 10000 and what message do you want to pop up?
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top