Log In
Register
Home
Active Member
Dashboards
Power Pivot
Power BI
August 2, 2016
Offline
Hi, I have a worksheet that triggers a message whenever the value in cell K4 or in L4 is greater than the value in cell O2. However, the message box pops up each time I change anything in the spreadsheet, which is frustrating. How can I get the message to pop -up only once, when the first change in the spreadsheet occurs on that day?
I am a newbie at VBA - any help is greatly welcome!
Here is my code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("k4").Value > Range("o2").Value Then
MsgBox "Sudden spike in usage this month!!", vbExclamation, Title:="Warning:"
ElseIf Range("l4").Value > Range("o2").Value Then
MsgBox "Sudden spike in usage next month!!", vbExclamation, Title:="Warning:"
End If
End Sub
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Gayatri,
Use Intersect to set the range to be monitored, this way the code will be triggered only when you change K4 or L4:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("K4,L4")) is nothing then
If Range("k4").Value > Range("o2").Value Then
MsgBox "Sudden spike in usage this month!!", vbExclamation, Title:="Warning:"
ElseIf Range("l4").Value > Range("o2").Value Then
MsgBox "Sudden spike in usage next month!!", vbExclamation, Title:="Warning:"
End If
end if
End Sub
Note that if K4 or L4 have formulas, and you don't actually make changes to those cells manually, the Change event will not be fired, a formula change is not a monitored event, in this case you need another logic.
Active Member
Dashboards
Power Pivot
Power BI
August 2, 2016
Offline
Thanks so much for replying, Catalin - truly grateful! However, the code did not work, because K4 and L4 have formulas, and the values are formula-driven, and are not manually changed. How can the code be modified to reflect this logic?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Gayatri,
In this case, instead of monitoring K4 and L4, put in code the ranges that you are actually editing manually that can cause changes in K4 and L4 formulas.
If you edit F2:F200 or example, and these changes might change K4 and L4 results, add this range in code:
If Not Intersect(Target,Range("F2:F200")) is nothing then
Active Member
Dashboards
Power Pivot
Power BI
August 2, 2016
Offline
None of the values are changed manually - they are formula driven.
I tried changing the event to calculate, ie as below:
Private Sub Worksheet_Calculate()
but now the macro does not work: I get the error message:
Procedure declaration does not match description of event or procedure having same name
Here is my code:
Private Sub Worksheet_Calculate()
If Not Intersect(Target, Range("M:M")) Is Nothing Then
If Range("k4").Value > Range("o2").Value Then
MsgBox "Sudden spike in usage this month!!", vbExclamation, Title:="Warning:"
ElseIf Range("l4").Value > Range("o2").Value Then
MsgBox "Sudden spike in usage next month!!", vbExclamation, Title:="Warning:"
End If
End If
End Sub
k4 and L4 are formulas that can change based on the values in column M
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Gayatri,
The worksheet_Change event provides an argument named Target:
Private Sub Worksheet_Change(ByVal Target As Range)
The calculate event does not provide any argument, so using the Target as an argument in Calculate is wrong:
Private Sub Worksheet_Calculate()
If Not Intersect(Target, Range("M:M")) Is Nothing Then
You have to rely only on worksheet ranges.
1 Guest(s)
