Run macro everytime cell content changes

[minn]

I want a macro to run everytime the value in say cell D6 changes.

Anyone know how to do this?

Many Thanks

[ashishprem]

goto macro window by pressing alt+F11
Select the sheet from left panel. there will be two drop down. from the left drop down select worksheet and from right drop down list select the event change.

to access rows and cols you can use following

Target.Worksheet.Cells(1, 1) 
Target.Row 
Target.Column

hope it helps..

Ashish

[minn]

Thanks, i have now tried using this:

Private Sub Worksheet_Change(ByVal Target As Range)
      
              If Target.Address = "$D$5" Then
                       Call Module3.View_All_Risks
              End If
 
End Sub

The problem now is that the macro does not fire when the value in cell D5 is changed. The macro run on its own works fine.

Any ideas?

Thanks!

[chucklod]

Hi,

You need to make sure the macro (which is an "event handler") is located in the code module for the worksheet it applies to.

Right click the worksheet (e.g., Sheet1) in the Project explorer and select "View Code". Copy the macro to the page that brings up. Then it should work any time the value in cell D5 is changed.

Chuck L

[minn]

Hi,

Tried that, no luck.

Here is the macro that I want to run:

Sub View_DatabyProjID()

Sheets("Actions Register").Select
Call Module7.RemoveFilter
Call Module7.AddFilter_actions
Selection.AutoFilter Field:=2, Criteria1:=Worksheets("Overview").Range("D6")

Sheets("Risks Register").Select 'goto the Risks Register worksheet
Call Module7.RemoveFilter 'remove all current filters
Call Module7.AddFilter_Risks 'reapply the filter
Selection.AutoFilter Field:=2, Criteria1:=Worksheets("Overview").Range("D6") 'filter by risks that correspond to the projectID that appears in worksheet 'Overview' and cell D6


Sheets("Issues Register").Select
Call Module7.RemoveFilter
Call Module7.AddFilter_Issues
Selection.AutoFilter Field:=2, Criteria1:=Worksheets("Overview").Range("D6")

Sheets("Overview").Select

End Sub

Thanks

[minn]

I have just realised that the problem is because the cell D5 where i want changed values to be detected is a validation list where values are changed via a drop down list.

I tested suing another cell where this was not the case.

Does anyone therefore know how to run a macro when the value in a cell that uses a validation list is changed?

Many Thanks