VBA run a macro when cell is changed, but not if by a macro

Question

OK I'm not sure if this is easily achievable but I'm going to try.

I use this sub to execute some macros if a cell is changed:

Private Sub Worksheet_Calculate()
Dim target As Range
Set target = Range("b4")
If Not Intersect(target, Range("b4")) Is Nothing Then
Call init
End If
End Sub

This works fine but I have a bit of a problem.

The cell B4, as referenced in the cell change sub above, has its value determined by a named range which is dynamic and contains a list of values on another sheet. I use the data validation tool to make B4 a dropdown list with the contents of the named range.

I have another macro who's purpose is to update this list. What it does is clear the current list, query a database and output a bunch of values into the range. The trouble is that when this macro is run it causes the value of B4 to change (as B4 references the values in the range). This in turn cause my "cell change" macro to run throwing up errors.

Is there a way to prevent the "cell change" macro from running while I'm updating the list that it references?

Hope that question makes sense.

brettdj brettdj · Accepted Answer · 2012-11-07T10:46:30

You can disable the Worksheet_Calculate Events by using Application.EnableEvents as below. Please note this will disable any WorkSheet or WorkBook event that may occur in-between Application.EnableEvents = False and Application.EnableEvents = True

So if your other sub was run like this - the Worksheet_Calculate event won't fire

Sub Other_Sub()
Application.EnableEvents = False
[b4].Value = "10"
'other stuff
Application.EnableEvents = True
End Sub

VBA run a macro when cell is changed, but not if by a macro

3 Answers