I have an Excel worksheet named “Data” and in it, a table called “tblData”. The data, consisting of 1 000 rows, was imported from an external data source.
I have added a number of extra columns to the table in which calculations based on very complex formulae are done.
When I make changes to the data in “tblData”, I have VBA code in Excel as follows:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
"my code goes here"
End Sub
But the code must not run if I click on Refresh All to refresh my data in the worksheet as I’m worried that the code will be triggered for every row of data that gets imported/refreshed.
Please provide a solution. Or will the code not be triggered during the Refresh All process?
Hi, simple way to do it is to declare a global variable
In the VBA project module you declare a Global boolean variable
Global isBusy As Boolean
In your refresh all module you set
isBusy = True
and before in exits you set isBusy = False
In the worksheet Sheetchange
the first like should then be
if isBusy = True then Exit Sub
I use this in my macros and events
There are many other ways but this is a quick method that works, don't forget to set it the variable to False
Hi Hans
Thank you for your help.
You suggested: "In your refresh all module you set..." I don't have a "Refresh All" module, as I click on Refresh All in the Excel Ribbon. Do you have a solution for that?
Greetings
Elmo
Resfresh all is for pivot tables and other data, not in a macro like you asked
