PDA

View Full Version : Recognizing linked changes, Worksheet_Change(ByVal Target As Range)



noff32
07-22-2014, 02:57 PM
I am trying to create code for a message box to appear when a value within my range is greater then 40. All of the values within my range are SUM() commands from the same sheet. The message box appears if I physically change the value of the cell (example, changing "SUM(G1:H16)" to simply "44") but will not change if I only change the values in the SUM() range (same example, just changing the numeric values in range H1:H16). I also do not want it to loop, if there are multiple errors I would want only one message box to appear. Below is the code that I have so far:


Private Sub Worksheet_Change(ByVal Target As Range)
Const FirstRow As Long = 57
Const LastRow As Long = 63
Dim myColumns As String
myColumns = "G:V"
If Target.Row >= FirstRow And Target.Row <= LastRow Then
If Target > 40 Then
MsgBox "This change has put you over 40 hours for the week.", vbOKOnly, "Warning"
Else
End If
End If
End Sub


Thank you!
Cody

dxider
07-31-2014, 01:29 PM
I am trying to create code for a message box to appear when a value within my range is greater then 40. All of the values within my range are SUM() commands from the same sheet. The message box appears if I physically change the value of the cell (example, changing "SUM(G1:H16)" to simply "44") but will not change if I only change the values in the SUM() range (same example, just changing the numeric values in range H1:H16). I also do not want it to loop, if there are multiple errors I would want only one message box to appear. Below is the code that I have so far:


Private Sub Worksheet_Change(ByVal Target As Range)
Const FirstRow As Long = 57
Const LastRow As Long = 63
Dim myColumns As String
myColumns = "G:V"
If Target.Row >= FirstRow And Target.Row <= LastRow Then
If Target > 40 Then
MsgBox "This change has put you over 40 hours for the week.", vbOKOnly, "Warning"
Else
End If
End If
End Sub


Thank you!
Cody

Add this code to the worksheet you want to be checked:


Private Sub Worksheet_Change(ByVal Target As Range)
' Filters column 7 and 8 (G & H)
' and checks only on rows 1 to 16
If Target.Column > 6 And Target.Column < 9 And Target.Row < 17 Then
If Target.FormulaR1C1 > 40 Then
MsgBox "Value greater than 40"
End If
End If
End Sub