CODEWITHSUNDEEP
excelvba
specmer
specmer

Reputation: 409

How do I disable Worksheet.Change event when Excel is open? Then re-enable it after Macro run

I have an SSIS package than runs every night and it produces an Excel file for my users. One user asked me to incorporate a Worksheet.Change event in one of the worksheet tabs so that he could track any changes that he makes.

How do I disable the Worksheet.Change event during the overnight production of the Excel file and then have it re-enabled for my user?

I tried adding the following line to the Worksheet.Change event:

If Range("B6").Value = "X" Then Exit Sub

My user then runs a Macro by pushing a button in the Excel file that deletes the "X" in B6, which would then starting highlighting cells that he changes.

This is the change event that I added to the worksheet tab

Public Sub Worksheet_Change(ByVal Target As Range)
     If Range("B6").Value = "X" Then Exit Sub
     Target.Interior.ColorIndex = 27
End Sub

This is part of the VBA macro that gets run when my user clicks the Macro button in Excel.

Sub clearCell()
     'Activates Sub Worksheet_Change in the Data tab by removing the X in B6
     'declare object variable to hold reference to cells to clear
     Dim myRange As Range
     'identify cells to clear
     Set myRange = ThisWorkbook.Worksheets("Data").Range("B6")
     'clear cells (including formatting)
     myRange.Clear
End Sub

For whatever reason, my VBA does not work. I've tried making changes in the worksheet tab and nothing highlights like it is supposed to. I would expect that since the "X" is cleared from B6 that any changes to the worksheet would be highlighted.

Upvotes: 0

Views: 2379

Answers (1)

Sam
Sam

Reputation: 5731

Changing a cell to stop the change event might lead to some unwanted effects. If you store the status outside the workbook, you are on the safer side.

If you test it like this:

Public Sub Worksheet_Change(ByVal Target As Range)
    If GetSetting("YourApplicationName", "Updates", "LoggingEnabled", "1") = "1" Then
        ...
    End If
End Sub

And set the flag with this:

SaveSetting "YourApplicationName", "Updates", "LoggingEnabled", "0"

you have the flag in the Registry, outside the workbook. Of course - you must have the events working (without flags) before trying this.
Also, rename the YourApplicationName part of it.

Upvotes: 1

Related Questions