18

I am fixing a spreadsheet. The programmer made a macro for each sheet to fire when the sheet is changed. This is good because it colour co-ordinates the sheet details when new information is added so I would like to keep this feature.

I have written a macro which sorts the data and allows for removal and addition of new employees, this is in conflict with the change event macro and is causing my macro to have errors if they are both operational.

Q. Is there a way to bypass the worksheet change event while the macro is running and then have it in place again once the macro is finished?

Here is the code for the change event.

Private Sub Worksheet_Change(ByVal target As Excel.Range, skip_update As Boolean)
If skip_update = False Then
    Call PaintCell(target)
End If
End Sub

My macro is bringing up errors when I refer to worksheets or ranges.

Community
  • 1
  • 1
user1545643
  • 313
  • 1
  • 4
  • 10

4 Answers4

44

I think you want the EnableEvents property of the Application object. When you set EnableEvents to False, then nothing your code does will trigger any events and none of the other event code will run. If, for example, your code changes a cell it would normally trigger the Change event or the SheetChange event. However, if you structure it like this

Application.EnableEvents = False
    Sheet1.Range("A1").Value = "new"
Application.EnableEvents = True

then changing A1 won't trigger any events.

Sometimes it's beneficial to have your code trigger event code and sometimes it's not. Use EnableEvents when you want to prevent it.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
14

This is an important modification to the answer from @Dick Kusleika.

When turning off the EnableEvents setting, it's a good idea to include error handling to turn EnableEvents back on. If you don't and the script throws an error, the change event trigger (your script) will stop working until you manually turn EnableEvents back on.

Ideally, you would place the following line immediately before any code that re-triggered your change event. The first line tells VBA to goto a label called "enableEventsOn" when it encounters an error. The second line bypasses change events.

On Error Goto enableEventsOn:
Application.EnableEvents = False

Then place this code immediately after code that re-triggered your change event. This turns change event triggers back on and returns normal error handling to your script.

Application.EnableEvents = True
On Error Goto 0

Finally, place this code at the end of your script. It's a label referred to above. If an error is encountered between "On Error Goto enableEventsOn:" and "On Error Goto 0" then the script will go here and turn EnableEvents back on, leaving it ready to start the script the next time you change your worksheet.

EnableEventsOn:
Application.EnableEvents = True
ChrisB
  • 3,024
  • 5
  • 35
  • 61
  • 3
    I have a failsafe sub that I generally insert and call at the end of other subs for this exact reason. Calculation automatic, screen updating on, events enabled, alerts displayed, etc. Had too many times where a sub errored out after turning a feature off and then I'm trying to figure out why Excel is imploding. – C-Love511 Feb 05 '20 at 18:12
  • 2
    @C-Love511 if you're interested, do a search for a custom class based approach. That's my favorite solution. Create a custom class that automatically restores events, screen updates, calculation mode, etc. in it's `Class_Terminasearch`: event. Then create a new instance of the class in a sub and when that sub ends, the class instance scopes out and settings are restored. Check out this CodeReview post: https://codereview.stackexchange.com/q/182278/115996 – ChrisB Feb 05 '20 at 22:43
1

Too risky in my own opinion.

I'd use a variable, named "skip_update" that is set to "True" in the beginning of each macro and to false when it ends.

e.g :

dim skip_update as Boolean = False

sub auto_macro_when_modify()
   if skip_update == True then
      exit sub ' NO CHANGE !
   end if
end sub

sub other_macro
    skip_update = True
    ' ( do stuff)
    skip_update = False
end sub
mansuetus
  • 64
  • 3
  • Hi, thanks for the reply, thats a great idea. I have tried implimenting it but it doesnt seem to take it into account. I'll post the code so it's clearer, but as soon as I put the change event back into the programme, my macro starts bringing up errors as soon as I've clicked run. And as soon as I remove the change event, it runs perfectly again. It seems to be in the preliminary checks before the programme even runs is when its bringing up errors. – user1545643 Apr 02 '13 at 10:40
  • Your "skip_update" has to be defined "global", that's outside any sub. The way you did it, the trigger might not be disabled. (I edited my answer too) – mansuetus Apr 02 '13 at 11:48
  • Hi, I've defined it as you have said, but its still a problem. I get application defined or object defined errors throughout my macro whenever the other macro in enabled, even with the is statement in there. – user1545643 Apr 02 '13 at 12:38
  • You may add "dim skip_update as boolean" outside any sub, but when "=false" is added it might crash. then, remove the " = false" part. That way, it should work :-) – mansuetus Apr 02 '13 at 17:09
0

You can hold down the CTRL key while doing an entry that would trigger the change on event routine. I have a routine to sum the values in a selected range in a given column. If I drag cursor from rows 1 to 5, highlighting them, then the sum of these is saved to the clipboard. Any entry in a cell in this column triggers the routine. If I hold down the CTRL key while doing the intended action or any entry in a cell in the column the routine is not initiated. May not work for more complicated ChangeEvent scenarios.

Malcolm
  • 1