Why Application.EnableEvents = False and =True in an event macro

[still learning]

Hi,

Way back in 2020 I got this from this site (Thank you) after asking how to have an event macro change a cell to proper case after typing in a text.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Application.EnableEvents = False
Target.Value = StrConv(Target.Value, vbProperCase)
Application.EnableEvents = True
End If
End Sub

I’m finally getting around to asking about it
Why do I need

Application.EnableEvents = False
and
Application.EnableEvents = True
AND
Why doing the macro without them closes the program?



mike

 

[Michael M]

Because it's a Change event.....everytime something gets changed, the code runs again, which makes the change again and it runs again....ad infinitum
By using those 2 lines it stops any change events until the changes have been done then reenables and exits the macro
Have a read here...

Using Application.EnableEvents in VBA in Excel (Examples)

Learn how to use Application.EnableEvents in Excel VBA with examples. Turn events on or off when running code to control workbook behavior.

spreadsheetplanet.com

 

[still learning]

Thanks Michael M
Very informative
I just checked and you were one of the two members that gave me this code

thanks again

mike

 

[Michael M]

Well there you go....and I'm still here...

 

[Ironman]

Hi Michael, are you still there ?

I read that the 'false' line needs to be inserted immediately after the 'dim' statement and the 'true' line inserted immediately before the 'End If'.

Can you just clarify if a false/true line is needed for each one of those within the change event or is it sufficient for the 'false' line just be inserted at the beginning of the first sub and before the end of the last one?

Many thanks!

 

[Michael M]

It needs to be inserted after the action has been completed.
If you put it at the end of the code you may get Incorrect results if other actions need to be carried out relative to a previous action

 

[Ironman]

Sorry Michael, which action? Would you mind explaining it in simple terms I can understand - once or 6 times please?

Thanks again!

 

[MARK858]

Sorry Michael, which action? Would you mind explaining it in simple terms I can understand - once or 6 times please?

I'm afraid Michael won't really be able to answer that as it depends on exactly what the various parts of your code are doing.
If you need other events to trigger then you would need to do it multiple times, if you only have the one event then only the once.

Can you just clarify if a false/true line is needed for each one of those within the change event or is it sufficient for the 'false' line just be inserted at the beginning of the first sub and before the end of the last one?

Your question here is a bit confusing , at the start you ask about within the change event and at the end about multiple subs.

In general if it is within the same event then you only need to set it / unset it once, but as I stated above it depends on exactly what you are doing in your code as there are occasions when you need to set it / unset it multiple times

If dealing with multiple subs then technically you could set it to false in the first sub and true in the last sub if you don't need any event codes running in between BUT it is a risky way of doing it, stick to setting it back to true as soon as each event has finished it's task.

 

[Ironman]

I'm afraid Michael won't really be able to answer that as it depends on exactly what the various parts of your code are doing.
If you need other events to trigger then you would need to do it multiple times, if you only have the one event then only the once.

Your question here is a bit confusing , at the start you ask about within the change event and at the end about multiple subs.

In general if it is within the same event then you only need to set it / unset it once, but as I stated above it depends on exactly what you are doing in your code as there are occasions when you need to set it / unset it multiple times

If dealing with multiple subs then technically you could set it to false in the first sub and true in the last sub if you don't need any event codes running in between BUT it is a risky way of doing it, stick to setting it back to true as soon as each event has finished it's task.

Many thanks Mark. I'll post back with the complete event in due course.

 

[jasonb75]

stick to setting it back to true as soon as each event has finished it's task.

Which can be equally risky if it is set back to true in one procedure / event and control returns to another that has not yet finished and still requires it to be set to false.

Personal preference in scenarios where multiple events / procedures are triggering each other is to check status of events and calculation as the first step, set them to false and manual respectively, then return them to their original status at the end rather than risk setting them back to true and auto too soon.