Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: semaphores only way to control events?

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Lawrence, KS
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Lawrence, KS
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again.
    If there is a way, I do not know it...
    If Then Exit Sub works for me!
    Tom

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •