Refreshing data in listbox on userform fires it's afterupdate event a second time

[progom]

Caveat - this is my first post after my introduction post.

Excel 2013. (VBA) I open a user form from a custom ribbon button. Form has listbox which is populated with service order data from an access database. On clicking on a listbox line item - the listbox_afterupdate event fires running code that in essence updates the selected service order status to "Scheduled" among other stuff. This all works fine.

What I want to happen is for the listbox data to be refreshed since the criteria for filling the listbox is the service order status is "Unscheduled". I.e., I don't want to have to close and re-open the form to update the data. This used to work, but while working with the code I did something to break it. I get the following error when the after update event runs again - "Runtime error 381...Could not get the column property. Invalid property array index." I understand why the error is there, I just don't want the event to run twice. Here is my code...

Please Login or Register  to view this content.

Thanks very much.

[AlphaFrog]

Hi and welcome to the forum

On clicking on a listbox line item - the listbox_afterupdate event fires

Not sure I follow what you are doing.
Clicking an item in a listbox doesn't trigger its own AfterUpdate event.
Why are you using the AfterUpdate procedure to update instead of the Click event procedure?

If you must use the AfterUpdate procedure to update the listbox and only want it to trigger once, you could toggle a Boolean variable and wrap the code in an IF code block so the 2nd time it runs, the update part of the code is ignored. Something like this...

Please Login or Register  to view this content.

[progom]

AlphaFrog: Click event works great. I knew I was making some simple mistake but couldn't see through the fog.

I appreciate your help.

progom