Ask a new question

Perform action when cell value changes

How can you instruct a macro to run when a certain cell changes?

For example, as soon as text in cell A1 changes, a macro is triggered.

Don't know if this is clear?

Answer
Answer

Hi,

You are right but I prefer the use of Worksheet_SelectionChange and store the value and test it later in Worksheet_Change to check if it has been changed or not. A lot faster then calling the application object.

Ronse thx for your suggestion, I didn't know about the performance about which one is faster. Let me also post your suggestion below

Dim oldValue As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
        oldValue = Target.Value
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
        If Target.Value <> oldValue Then
            'Do something
        End If
    End If
End Sub

Wyman W
Human Resources

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

Hi,

You are right but I prefer the use of Worksheet_SelectionChange and store the value and test it later in Worksheet_Change to check if it has been changed or not. A lot faster then calling the application object.

Kind regards,

JP Ronse

If this response answers your question then please mark as Answer.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 307 Applies to: