none
Excel 2013 listbox events: "BeforeUpdate" or "Change" but not both?! RRS feed

  • Question

  • So I have a simple listbox on a simple UserForm and I have the following code behind the form

    Private Sub lstParcels_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
        MsgBox "Before Update"
    End Sub

    Private Sub lstParcels_Change()
        MsgBox "Change"
    End Sub

    With this code when I click to a new item in the listbox the "Change" event is triggered but the "BeforeUpdate" isn't. At all --  never -- period. If I comment out the line in the "Change" event, the "BeforeUpdate" runs as expected. I would expect both to fire each time I move, but it looks like a 'Change' event overrides/disables a 'BeforeUpdate' event. Has anyone else seen this behavior? (As you may guess, the actual code I need to run is far more complex, but the key element is that I need to reliably trap these events individually to set variables and, based on their values, decide whether to cancel the update. )

    = Mike =

    Saturday, July 4, 2015 1:24 AM

All replies

  •  based on their values, decide whether to cancel the update. )

    Hello Mike,

    I don't think you will find that setting Cancel to True in the Before Update event will cancel the update. It prevents the user moving off the control.


    Regards, OssieMac

    Saturday, July 4, 2015 7:27 AM
  • OssieMac,

          Thanks for the reply. Seems I confused the question by suggesting what I was going to do within each event. Sorry about that! The core question is that is seems in my environment at least (Excel 2013 64 bit running in WIndows 8.1 64 bit) having a "Change" event for a listbox disables the "BeforeUpdate" event for the listbox. That's the situation I'm wondering if others have seen and, if they have, whether there's some sort of workaround. I'm not seeing anything on the MS web site suggesting this is what happens.

    = M =

    Sunday, July 5, 2015 10:22 PM
  • having a "Change" event for a listbox disables the "BeforeUpdate" event for the listbox. That's the situation I'm wondering if others have seen and, if they have, whether there's some sort of workaround. I'm not seeing anything on the MS web site suggesting this is what happens.

    That is what occurs. Whether by design or not I don't know. However, you should be able to write code to handle the situation without using both events. Your comment in your initial question "decide whether to cancel the update" will need code to handle and not simply set cancel to true.

    If you cannot write the code, then describe what it is you want to do and I will try to assist.


    Regards, OssieMac

    Monday, July 6, 2015 12:11 AM
  • Thanks for the confirmation of the anomaly in the Event handling. Writing the code won't be the problem -- though thanks of the offer -- now that I know it's not something unique to this environment.

    = M =

    Monday, July 6, 2015 3:39 PM