automatically execute an Excel macro on a cell change

excel vba automation
namin picture namin · Jan 3, 2009 · Viewed 493.3k times · Source

How can I automatically execute an Excel macro each time a value in a particular cell changes?

Right now, my working code is:

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

where "H5" is the particular cell being monitored and Macro is the name of the macro.

Is there a better way?

Answer

Mike Rosenblum picture Mike Rosenblum · Jan 6, 2009

Your code looks pretty good.

Be careful, however, for your call to Range("H5") is a shortcut command to Application.Range("H5"), which is equivalent to Application.ActiveSheet.Range("H5"). This could be fine, if the only changes are user-changes -- which is the most typical -- but it is possible for the worksheet's cell values to change when it is not the active sheet via programmatic changes, e.g. VBA.

With this in mind, I would utilize Target.Worksheet.Range("H5"):

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

Or you can use Me.Range("H5"), if the event handler is on the code page for the worksheet in question (it usually is):

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

Hope this helps...

Related questions

Excel VBA Open workbook, perform actions, save as, close

This question has been edited due to lengthy comments and updates from proposed answers. As requested here is module 13; Sub SaveInFormat() Application.DisplayAlerts = False Workbooks.Application.ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\jammil\Desktop\AutoFinance\ProjectControl\Data\" & Format(…

Read More
VBA interaction with internet explorer

The macro I am building takes names from an Excel spreadsheet, opens Internet Explorer, and searches the online directory. After searching the directory, it pulls up a Java form with the manager's name in it. I am able to manually …

Read More
what is a good way to autorun macros upon open

I've tried using the code that many sites have suggested to autorun a list of macros upon opening an Excel workbook. Attached is my VBA code on ThisWorkbook: Private Sub WorkbookOpen() MsgBox "STOP! Do NOT attempt to highlight any fields …

Read More