none
WithEvents doesn't handle all events?

    Question

  • I'm in VBA for Excel 2007. I'd like to add a TextBox at runtime and handle its events. I set up a class, declared a Public WithEvents myBox As MSForms.TextBox, and went to find the event. But not all the events are in the drop-down window! On the TextBoxes I add at designtime, the dropdown lets me select any of these events:

    AfterUpdate

    BeforeDragOver

    BeforeDropOrPaste

    BeforeUpdate

    Change

    DblClick

    DropButtonClick

    Enter

    Error

    Exit

    KeyDown

    KeyPress

    KeyUp

    MouseDown

    MouseMove

    MouseUp

     

    However, when I go to select an event for my MSForms.TextBox property inside my class, it has conspicuously omitted AfterUpdate, BeforeUpdate, Enter, and Exit. Unfortunately, Exit is precisely the event I need. I want to have a message box that barks at the user if they enter a bad value and cancels the exit.

    I tried just typing the exit code as if it would respond without being in the drop-down, but it just behaved as though this was a strange method it didn't care about. I've searched a few forums, but I get a lot of stuff telling me how to set up the control "WithEvents" in the first place and nothing telling me why it doesn't handle all events or what I can do about it. As always, any help would be greatly appreciated.

     

    Thanks.

    Thursday, June 09, 2011 2:55 PM

All replies

  • Indeed the omitted events you listed are not exposed in a WithEvents Class for a
    TextBox (and a few other controls). Normally it's possible to work around but it can be tedious!

    Peter Thornton

    • Proposed as answer by Shasur Friday, June 10, 2011 2:52 AM
    Thursday, June 09, 2011 4:35 PM
    Moderator
  • The only work-around I can think of would be to try to catch any attempt to leave the control by using KeyUp and MouseUp events. Is this the best tedious solution, or is there something more elegant? ... By the way, I don't need to declare it as a property of a class for any other reason than to catch the event. If there was a way to use the Handles keyword or something like that, I'm willing to do it; I just couldn't get it to work in this version.

    Even if there isn't a better way around this, why is it exactly that these events are not exposed?

     

     

    Thanks.

    Thursday, June 09, 2011 4:42 PM
  • Indeed you're thinking along the right lines though with key events you'll be looking for  a tab keydown which will tell you you're about to loose focus, or keyup to gain focus. Mouse is more convoluted, will only get a keyup to tell you've got focus.

    The other thing to think about is everything you're doing, can you achieve similar a different way.

    Peter Thornton
    (I won't be back until tomorrow)

    Thursday, June 09, 2011 4:53 PM
    Moderator
  • Even if there isn't a better way around this, why is it exactly that these events are not exposed?

     

    I'm fighting this same battle. The only explanation I've come across is that these events are inherited from another class when added at design time. Apparently this inheritance is not possible when declaring them at run time using WithEvents.

    Thursday, June 16, 2011 2:24 PM
  • I'm fighting this same battle. The only explanation I've come across is that these events are inherited from another class when added at design time. Apparently this inheritance is not possible when declaring them at run time using WithEvents.

    All the events are inherited, it's just that some are not exposed to anything other than the host userform class.

    As I mentioned to the OP it's normally possible to work around, albeit a bit tedious if you have several other controls on the form. Trapping Enter/Exit should be doable, post back if not sure how to approach.

    Peter Thornton

    Thursday, June 16, 2011 4:05 PM
    Moderator
  • I'm in VBA for Excel 2007. I'd like to add a TextBox at runtime and handle its events. I set up a class, declared a Public WithEvents myBox As MSForms.TextBox, and went to find the event. But not all the events are in the drop-down window! On the TextBoxes I add at designtime, the dropdown lets me select any of these events:

    AfterUpdate

    BeforeDragOver

    BeforeDropOrPaste

    BeforeUpdate

    Change

    DblClick

    DropButtonClick

    Enter

    Error

    Exit

    KeyDown

    KeyPress

    KeyUp

    MouseDown

    MouseMove

    MouseUp

     

    However, when I go to select an event for my MSForms.TextBox property inside my class, it has conspicuously omitted AfterUpdate, BeforeUpdate, Enter, and Exit. Unfortunately, Exit is precisely the event I need. I want to have a message box that barks at the user if they enter a bad value and cancels the exit.

    I tried just typing the exit code as if it would respond without being in the drop-down, but it just behaved as though this was a strange method it didn't care about. I've searched a few forums, but I get a lot of stuff telling me how to set up the control "WithEvents" in the first place and nothing telling me why it doesn't handle all events or what I can do about it. As always, any help would be greatly appreciated.

     

    Thanks.


    I'm not trying to discourage you from continuing down your chosen path.  At the same time, I find that relying on events like Exit (or Mouseup) for validation yields an inconsistent and unreliable approach.  This is because some events are not triggered when one might expect them.

    My approach is to wait until the consumer is ready to submit the information.  This gives her/him a chance to correct errors without what I believe are annoying and premature alerts.  When the consumer submits the data, validate the data.

    For more on this approach see

    Userform Interface Design

    http://www.tushar-mehta.com/publish_train/book_vba/09_userinterface.htm


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Friday, June 17, 2011 3:11 PM