how to Run VBA code on cell update/change

Question

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!

If the change is due to formula recalculation, then you have to use the Worksheet_Calculate event instead of the Worksheet_Change event. — BigBen
– BigBen, Commented Feb 26, 2021 at 15:50
@BigBen It is, however when I change that to calculate I get "Compile error: Procedure declaration does not match description of event or procedure having the same name" — Ranjar
– Ranjar, Commented Feb 26, 2021 at 15:53
Right. Because Worksheet_Calculate has no Target parameter. — BigBen
– BigBen, Commented Feb 26, 2021 at 15:53
@BigBen So do i jut remove the "If not intersect..." or simply the (Target...)? if you could reply as an answer with the code modified this would be extreamly helpful :) — Ranjar
– Ranjar, Commented Feb 26, 2021 at 16:01
Since J1 is dependent on A1, couldn't you just use A1 as the target of your code? If Not Intersect(Target, [A1]) Is Nothing Then ... You can still use the value of J1 to drive the color of the Activex Objects. — basodre
– basodre, Commented Feb 26, 2021 at 16:17

BigBen · Accepted Answer · 2021-02-26 19:08:18Z

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

Collectives™ on Stack Overflow

how to Run VBA code on cell update/change

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related