semaphores only way to control events?

dhoffman

New Member
Joined
Apr 1, 2002
Messages
29
I've noticed that with events such as Worksheet_Change(ByVal Target As Range) that they are fired whether the user makes the change or code makes the change. Of course one might wish code under this event to only run if the user makes a change but not if other code makes a change to the worksheet. Is there any way for code to distinguish whether an event was user initiated or code initiated? Probably not, I believe. One could, at the beginning of a routine that would cause Worksheet_Change to fire, turn off events by setting Application.EnableEvents to false, right? And then trun them back on at the end of the routine. But what if it is only desired to turn off one event, what if it is necessary that others be able to fire, say a textbox_change event for instance. I suppose it may not be necessary for other events to be enabled since they can always be called manually once code is running, but it might be more convenient. Besides turning events off one might create a semaphore as an integer. As long as it is zero all events/code that should run only upon user action would be able to run. Every time a routine begins that may fire an event containing code that shouldn't then be executed would increment the semaphore, and each time such a routine ended it would decrement the semaphore. I believe that's a common method for preventing certain code from running. But it could involve adding extra code to the beginning and end of a lot of routines and makes things more complicated. So does anybody know how to turn off a specific event temporarily?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
I think the semaphore method works fine.
I have never had a problem using flags in my code...
Many times I have used bytes instead of just true or false...
It gives you great flexibility in dealing with events.
As far as the added code, there is usually very little code involved.
Create one seperate procedure to deal with all of your flags.
Pass a Public value to the procedure and then make your decisions from there...
Tom
 
Upvote 0
It seems that setting
Application.EnableEvents = False
only turns off events directly associated with the workbook and worksheets, etc. That is, the events tied to controls attached to the worksheet still fire as always. I believe the controls are ActiveX controls, they are just buttons and dropdown boxes. Can the events associated with those controls be turned off in a similar way to the Application events?
 
Upvote 0
Hi again.
If there is a way, I do not know it...
If Then Exit Sub works for me!
Tom
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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