VBA Worksheet change event bypass?

vba excel worksheet
user1545643 picture user1545643 · Apr 2, 2013 · Viewed 50.1k times · Source

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.

Answer

Dick Kusleika picture Dick Kusleika · Apr 2, 2013

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.

Related questions

How to add a named sheet at the end of all Excel sheets?

I am trying to add an Excel sheet named "Temp" at the end of all existing sheets, but this code is not working: Private Sub CreateSheet() Dim ws As Worksheet ws.Name = "Tempo" Set ws = Sheets.Add(After:=Sheets(Sheets.…

Read More
Excel VBA For Each Worksheet Loop

I am working on code to basically go through each sheet in my Workbook, and then update column widths. Below is the code I wrote; I don't receive any errors, but it also doesn't actually do anything. Any help is …

Read More
Is there a macro to conditionally copy rows to another worksheet?

Is there a macro or a way to conditionally copy rows from one worksheet to another in Excel 2003? I'm pulling a list of data from SharePoint via a web query into a blank worksheet in Excel, and then I want …

Read More