cell value change not getting captured in vba macro

Question

I want to capture when value changes in the cell A2

I tried the below code but it is not working

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("H5")) Is Nothing Then Macro
End Sub

I have a breakpoint inside the macro but its not coming till there I also tried the below, but no luck

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox (Target.Column)
End Sub

stucharo · Accepted Answer · 2015-04-29 14:04:44Z

1

Worksheet_Change takes a single parameter Target of type Range. To check if you have changed the range you are interested in you can compare the target to that range, in your case:

If Target = Range("A2") Then
    'Do something
End If

This lets you compare a Range object with another Range object with minimal fuss.

And also put it in the correct sheet.... :P

answered Apr 29, 2015 at 14:04

stucharo

8655 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vignesh Subramanian · Accepted Answer · 2015-04-29 14:01:42Z

0

I found out the reason

I placed the macro in wrong place i.e in a different module

For the Private Sub Worksheet_Change to work, we need to place it in the sheet which needs that detection I followed the below steps

We need to go to Excel VBA editor by pressing ALT + F11
Double click on sheet which contains our target cell e.g. Sheet1
Add the below code to the sheet

answered Apr 29, 2015 at 14:01

Vignesh Subramanian

7,31915 gold badges98 silver badges161 bronze badges

Collectives™ on Stack Overflow

cell value change not getting captured in vba macro

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related