Trigger code to edit a cell after a value entered in a cell

Question

I wrote a macro to check the value being entered in some cells.

If the input is higher than 8 the excess is written to another cell and the input is changed to 8. If the input is lower than 8 the missing amount is written to a third cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
TA = Target.Address: R = Target.Row: C = Target.Column
If C = 2 Or C = 7 Then
  If (R < 19 And R > 11) Or (R < 33 And R > 25) Then
    Hours = Cells(R, C).Value
    If Hours <> 0 Then
      If Hours > 8 Then
        Cells(R, C) = 8
        Cells(R, C + 1) = Hours - 8
      End If
      If Hours < 8 Then
        Cells(R, C + 2) = 8 - Hours
      End If
    End If
  End If
End If
End Sub

The problem is the macro is not executed when I enter the input, only when I select the cell again.

score 1 · Answer 1 · edited May 23 '17 at 12:01

1

Your function Worksheet_SelectionChange only fires when the selected cell is changed. You should use Worksheet_Change instead. You can see this automatically execute an Excel macro on a cell change for more details.

edited May 23 '17 at 12:01

Community

1
1

answered Sep 13 '16 at 08:15

Tommy Tang

111
8

Shai Rado · Accepted Answer · 2016-09-13T08:24:48.753

First change your trigger event from Worksheet_SelectionChange to Worksheet_Change.

Second, you can optimize your code, since you can read the Column and Row property of Target, you can save a few rows in your code.

Third, I modified your test condition for checking the row, by switching to Select Case you can now add more rows to this condition easily.

Use Target.offset to insert the result in the neighbour cells.

I added Exit Sub so it won't run an extra time after you change the values here.

If you want, you can also remove the Hours as it is not needed (unless you have a global variable that somehow reads this value).

You can just use If Target.Value <> 0 Then etc.)

Private Sub Worksheet_Change(ByVal Target As Range)

' check if target is in Column B or Column G
If Target.Column = 2 Or Target.Column = 7 Then

    Select Case Target.Row
        Case 12 To 18, 26 To 32  ' check if target row is 12 to 18 (including) ir between 26 to 32 (including)

            Hours = Target.Value
            If Hours <> 0 Then
                If Hours > 8 Then
                    Target.Value = 8
                    Target.Offset(0, 1).Value = Hours - 8
                    Exit Sub
                Else
                    If Hours < 8 Then
                        Target.Offset(0, 2).Value = 8 - Hours
                    End If
                    Exit Sub
                End If
            End If
    End Select
End If

End Sub

Trigger code to edit a cell after a value entered in a cell

2 Answers2