VBA runs a macro when a cell is changed, but not if the 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 the 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 great, but I have a problem.

Cell B4 , as indicated in the above cell change element, has its own value defined by the named range, which is dynamic, and contains a list of values ​​on another sheet. I use a data validation tool to make a B4 drop-down list with the contents of a specified range.

I have another macro whose purpose is to update this list. What he does is clear the current list, query the database, and display a range of values ​​in the range. The problem is that when this macro runs, it causes a change in the value of B4 (since B4 refers to values ​​in the range). This, in turn, leads to the fact that the macro "cell change" triggers errors at startup.

Is there a way to prevent the "change cell" macro from executing while I am updating the list it references?

Hope this question makes sense.

Answer 1

You can disable Worksheet_Calculate events using Application.EnableEvents , as shown below. Note that this will disable any WorkSheet or WorkBook events that may occur between Application.EnableEvents = False and Application.EnableEvents = True

So, if your other south was fired as follows: the Worksheet_Calculate event does not fire

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

Answer 2

Nothing, I developed a simple solution: Just put the conditional statement in the statement so as not to execute “init” if B4 contains an error or is empty.

Answer 3

and know (exit sub) or (exit function) ... Remember to use the Application.EnableEvents = True commands before (exit sub) or (exit function) (if exists)