prevent EnableEvents not reset to true

fnijkampnl

New Member
Joined
Aug 26, 2023
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Hi,
as it happens for a registration appl. I use a lot of macro's.
They call each other and activate or change other sheets.
Therefor I set EnavleEvents to false to start with.
And I set it to true to end the programs.

At least, that's what I think.
When another user updates the sheet, I notice that certain macro's are not started, like "Logging".
So events are still off.

How can I make it so that Data-entry is not possible when macro's are running or not properly ended?
Can the UI be blocked ie?
I can use 2 cells; one with =Now() and one filled by a macro with NOW(). And then some action at a discrepancy.

grtz Frank
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You need to be careful with EnableEvents in VBA code. It is not always as simple as placing code at the top of the procedure to turn it off, and then placing code at the end to turn it back on.
If you have sections of code that will exit before it gets to the end, it will never turn them back on.
There may also be certain sections of code making changes that you DO want to trigger events.

So you really need to think about it carefully, and program your EnableEvents code with a little more precision.
I usually try to limit it as much as possible. For example, if I have a section of code that is making a change that I do NOT want to trigger events, I will simply wrap the events code around that changes, structured something like this:

Rich (BB code):
'A whole bunch of code here

Application.EnableEvents = False
' My code making the particular change here
Application.EnableEvents = True

'The rest of my code here

Also note that if your code errors out someplace, it may never get to the line of code that re-enable events.
 
Upvote 0
Not an answer to my question but thx anyway.
But now I've more solutions meanwhile.

About the events: I made it so that each procedure with changes sets the events off and sets a specific unique number. Other called procedures can only set events on or off if they provide the matching number.
 
Upvote 0
It sounds like you have some issues with your code, and your solution is to try to work around those errors/issues, as opposed to fix the problem that is causing them in the first place.
My recommendation is to fix what is causing the issue in the first place, rather than gloss over and "brute force" over it. If you are not sure what is happening that causes the situation, there is a risk you could create more of those scenarios as you add more code, and then have to keep adding more "patches/fixes", and you end up with a whole bunch of code that you really do not need.

This is a common example of an issue that I see, in simplified form:
Rich (BB code):
Sub SomeProcedure

    Application.EnableEvents = False

    Some Code

    If SomeCondition Then
        Exit Sub
    End If

    Some More Code

    Application.EnableEvents = True

End Sub

So, the person thinks that they have set it up properly, by disabling events at the beginning of their code, and then re-enabling them at the end.
But if they have some condition in the middle of their code that exits the sub before it gets to the end (like an "Exit Sub" line), the code will exit before getting to the line of code that re-enables events.
They would need to make sure that they re-enable events before exiting the code if they exit before the natural end of the code.
 
Upvote 0
Solution
Not an answer to my question but thx anyway.
But now I've more solutions meanwhile.

About the events: I made it so that each procedure with changes sets the events off and sets a specific unique number. Other called procedures can only set events on or off if they provide the matching number.
Note: That is not bad "fail-safe" to have in place to ensure things don't happen when you don't want them to.
My point is that if your code is not working as you expect it, you may want to try to figure out why and fix it, as it could have other negative ramifications on your workbook's performance.
We can always help you analyze your code and spot problem areas, if you want to post it.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top