How to set up a pop up message when sum of columns exceeds X
Category: [Excel] Demo Available
How to set up a pop up message when sum of columns exceeds X
Requirement:
The user is trying to write code that would pop alert when sum of columns exceeds 75.
Data is from Column A to Column AB. Alternate columns have hours that gets summed up at AD.
The user wants a pop up alert as soon as there is entry in A-Ab which would result in AD >75.
Solution:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim answer As Integer
Dim q As Integer
Dim c As Integer
c = Target.Row
If c < 56 Then
If Evaluate("=SUM(A" & Target.Row & ":" & "AB" & Target.Row & ")") > 75 Then MsgBox " This employee is reaching Casual hours of 80 hours. More allocation of work may result in 'Casual-Overtime' "
Else
If Evaluate("=SUM(A" & Target.Row & ":" & "AB" & Target.Row & ")") > 24 Then
answer = MsgBox("Too much overtime for this employee. It's been 24 hours of OT already. Do you wish to continue ?", vbYesNo + vbQuestion)
If answer = vbYes Then
q = MsgBox("Fill the fatigue questionnaire", vbOKOnly): Exit Sub
Else
If MsgBox("Undo last change", vbOKOnly, "Undo last change") = vbOK Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If
End If
End If
End Sub
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1216609-pop-up-message when-sum-of-columns-exceeds-x
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to bring an excel userform message box above all other windows |
| How to create a pop up message if the length exceeds the limits |
| How to display a message for each if the value is greater than the mentioned values |
| How to create excel VBA code to ascertain if cell value is greater than 2 cell values, show a pop up message |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.