UserForm Event Fires Randomly

[Boswell]

I have a workbook with a rather complicated series of user forms. One of the forms consists of a list box with an associated click event that fires randomly (or for reasons i do not understand). I have tirelessly stepped through the code to verify that the list box is *not* being "clicked" or altered programmatically when it is firing.

The list box click event tends to fire when a range on a worksheet is calculated. Additionally, it fires 20+ times upon the user form initialization event. I set the contents of the list box via row source and even if there are only a few items added upon initialization the list box click event still fires 20+ times.

Is anyone familiar with this problem.. are there some coding best practices I am overlooking? The code is too circuitous to post; however, I am an experienced VBA programmer and have ruled out any obvious issues (such as nested events that are calling each other back and forth)

Any suggestions would be appreciated.

 

[Boswell]

Rorya, I am going to switch to your suggested method of populating the listbox; it will probably take me a while get everything sorted in order to confirm if that resolves my particular problem.

Haha Hermanito, I actually did close Excel and reboot my PC earlier hoping that the issue would just resolve itself.

 

[Boswell]

Yes, Row Source is definitely the issue. Its going to be a pain to rework the populating of the list box (my list box has multiple columns which has proven to be difficult ... probably why i went with Row Source when i originally set it up) but eliminating row source will solve my problem.

Thank you both for your help.

 

[Hermanito]

For multicolumn lists, I prefer to use ListView instead of ListBox. It has more flexibility I think...

Good luck with the refactoring!

 

[RoryA]

Multiple columns are just as easy with List - just assign the whole range to it.

 

[Boswell]

My list box has 3 columns. My range is continuous and also consists of 3 columns (variable number of rows)... yet the following assignment kicks back an error:

UserForm1.ListBox1.List = Worksheets("MyWorksheet").Range("MyRange").Value

Specific Error: Run-time error '70' Permission denied.

Am I approaching this wrong... are there any obv errors in the above code?

Thanks

 

[RoryA]

Do you still have the Rowsource property set?

 

[Boswell]

argh... yes, i was inadvertently setting the rowsource programmatically and then trying to use the .List assignment. I found and eliminated the method that was setting the rowsource property and all seems to be working smoothly. Thanks again.

 

[RoryA]

Glad to help.

 

[Boswell]

I need to grab values from the columns individually. The method below was working when using row source property, but now returns error "Could not get list property Invalid argument"

myItem = ListBox1.List(ListBox1.ListIndex, 2)

If I remove the 2, or replace the 2 with zero, I get no error. However it then returns the value located in the first column of the first row (.listindex = 0). Also this is being called from the click event which is triggered by

UserForm1.ListBox1.List = rngFiltered.Value

where rngFiltered is a continuous range with three columns and a variable number of rows. Is there a better way of accessing values by column?

 

[Boswell]

Sorry... Jumped the gun on that last post. rngFiltered was only being set equal to a one column range. Issue resolves when rngFiltered is properly set.