How to turn off "activate when cell changed" while other macros change cells?

[thk12205]

Excel Version: Professional Plus 2013

I've made a macro for a spreadsheet that displays information and respective dates from data log.
I also wanted to make it so that any changes made on the display would save to the log.
The macro would fire everytime a different macro ran, so I had to wrap all the other macros in "Application.EnableEvents = False/True".
However, now its not even firing.
How would I make a macro run each time a cell is changed, but not have it activate when other macros change a cell?

The macro for log update:

Sub Worksheet_Change(ByVal Target As Range) ''''''''''''''''''''''''''''''' SetProjectVariables
requestorNum = Range("B3").Value

If Not Intersect(Target, Range("B6:K120")) Is Nothing Then
rowActiveCell = ActiveCell.Row
colActiveCell = ActiveCell.Column

ws3log.Cells(rowActiveCell - ws3First + ws3logFirst, colActiveCell + 1 + 10 * (requestorNum - 1)) = ActiveCell.Value
End If
End Sub

[Paul_Hossler]

In the other macros



Application.EnableEvents = False

….. Updates ….

Application.EnableEvents = True

[SamT]

Some Macro is exiting before the EnableEvents = True line

I see a lot of that, so I have a very handy Sub where I can get to it easily

Sub ResetApplication()
With Application
.EnableEvents = True
.ScreebnUpdating = True
.Calculation = xlAutomatic 'Technically, this is redundant, but Ram is cheap.
'Add other as you see fit
End With
End Sub

It is my opinion that it is bad practice to use an Event sub, Like Worksheet_Change, to do anything other than look at the Target(s) and call the appropriate Sub.

Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Intersect(Target, Range("B6:K120")) Is Nothing Then
SetRequesterNum Target
'ElseIf Target.Address = "$Z$99:$Z$101" Then
'AnotherSub Target
'ElseIf SomeOther Target Then
'Some other sub Target
End If

Application.EnableEvents = True
End Sub


Private Sub SetRequesterNum(Target As Range)
requestorNum = Range("B3").Value

rowActiveCell = Target.Row
colActiveCell = Target.Column

ws3log.Cells(rowActiveCell - ws3First + ws3logFirst, colActiveCell + 1 + 10 * (requestorNum - 1)) = Target.Value

End Sub
'------------------------------------------------------------------------------

Private Sub AnotherSub(Target as Range)
'Do stuff to or with Target, (Range("$Z$99:$Z$101"...) Supposedly ;)
'For example... X = WorksheetFunction.SUM(Target)
End Sub
'--------------------------------------------------------------------------------

Private Sub SomeOtherSub(Target As Range)
'
End Sub

[thk12205]

Wow, ResetApp() worked like a charm. Thank you!

I looked around, but couldn't find the open Application.EnableEvent, but using ResetApp(), that is no longer a problem.

[SamT]

Try hard to find the bug in your code
In Each Sub (or Function) use a simple MsgBox before Events = False and Events = True

MsgBox"Setting Events in Sub Name"
...Events = False
'
'regular sub code
'
MsgBox"Resetting events in Sub Name"
...Events = true

That being said, some people use a Standard module, with a name like "Common_Procedures" to hold many often used routines. Then, they just Drag or Import that module into every Project. That is a good place for your Private Sub ResetApplication()... NOTE!!! ResetApplication should only ever be manually triggered and only when you have buggy code.

One Sub you can reasonably put in Common_Procedures is

Public Sub SpeedUp(ResetApp As Boolean)
With Application
.EnableEvents = Not ResetApp
.Calculation = Not ResetApp
.ScreenUpdating = Not ResetApp
'Add others as you need
'If you have timing issues because ie: Calculations take too long, add
'DoEvents
End With
End Sub

This Sub replaces every EnableEvents type code in your other code. To use it

Sub MyProcedure()
SpeedUp True
'
'Regular Code
'
SpeedUp False
End Sub

SpeedUp True is mnemonic for "Yeah, I want my code to run faster than usual"
SpeedUp False is mnemonic for "Ok, Run the application normally."

Some people think FastCode is a better mnemonic than SpeedUp. YMMV