how to Run VBA code on cell update/change

user_15038108 0
Viewed 90

so my code below works for changing activex control command buttons from red to green depending on the value in J1 however J1 value changes.

Now I would like my code to update depending on if J1 have changed. is there a way i can force this to run again after the cell J1 has changed?

    Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, [J1]) Is Nothing Then
For Each obj In ActiveSheet.OLEObjects
If obj.Name = "CommandButton8" Then
With obj
If [J1].Value >= 1 Then .Object.BackColor = RGB(0, 255, 0)
If [J1].Value = 0 Then .Object.BackColor = RGB(255, 0, 0)
End With
End If
Next
End If
On Error GoTo 0
End Sub

Thank you so much!

excel-formula vba excel
1 Answers
  • The Worksheet_Change event does not fire when formulas are recalculated. You need to use the Worksheet_Calculate event for that scenario. The Calculate event has no Target parameter.
  • A blanket On Error Resume Next hides all potential errors and is considered bad practice.
  • Me in a sheet code-behind refers to that sheet and is preferable to ActiveSheet.
  • You can use the constants vbGreen and vbRed instead of the RGB function.
Private Sub Worksheet_Calculate()
    For Each obj In Me.OLEObjects
        If obj.Name = "CommandButton8" Then
           If Me.Range("J1").Value >= 1 Then 
              obj.Object.BackColor = vbGreen
           ElseIf Me.Range("J1").Value = 0 Then 
              obj.Object.BackColor = vbRed
           End If
        End If
    Next
End Sub
user_92458530
Related