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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With