Reputation: 409
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
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