Active X Combo Box Filter Endlessly Looping

[SHUTTEHFACE]

Hi!

I'm using the following code which is resulting in an endless loop until excel crashes. It also doesn't seem to be filtering to the cmdShiftFilter value selected (1,2,3,* for all):

Please Login or Register  to view this content.

Thanks for the help

M

[Norie]

How is the combobox populated?

[SHUTTEHFACE]

In the properties I set Listfillrange = ShiftList (which is my named range for the shifts list)

[Norie]

Is ShiftList on the same worksheet as the rows you are hiding?

If it is then what could be happening is that every time you hide a row the change event is being triggered and that causes you to go into the endless loop.

[SHUTTEHFACE]

No ShiftList is on a different worksheet called "Calculation Lists".

[Norie]

How is ShiftList defined?

Also, where is the endless loop you refer to?

As far as I can see the Do While loop in your code will be finite, stopping when the first blank cell in column 1 is found.

[SHUTTEHFACE]

Just a dynamic range

=OFFSET('Calculation Lists'!$M$2,1,0,COUNTA('Calculation Lists'!$M:$M)-1,1)

[SHUTTEHFACE]

How is ShiftList defined?

Also, where is the endless loop you refer to?

As far as I can see the Do While loop in your code will be finite, stopping when the first blank cell in column 1 is found.

When I change the value in cmbShiftFilter to 1,2,3,etc...

The rows keep filtering down to nothing 1 by 1 until they are all hidden. Then it start over... endlessly. It doesnt actually seem to be filtering correctly to the value selected.

[Norie]

That could be the problem as hiding rows could be triggering the formula to calculate which in turn could be triggering the change event again.

Hard to tell without seeing the workbook though.

[SHUTTEHFACE]

Ah, I think I understand what you are saying. Is there a way to avoid this other than changing the _Change() location? I'd like to have the change in the combobox automatically trigger the filter instead of having to click a button.

[Norie]

You could change the way the combobox is populated so that it's not 'tied' to the dynamic range.

It would still be used for population, but there wouldn't be the 2-way link between the range and combobox.

One way of doing that would be to use a simple line of code like this.

Please Login or Register  to view this content.

That could go in the activate event of the sheet the combobox is on, and could also be used elsewhere if, for whatever reason, you need to repopulate the combobox.

[SHUTTEHFACE]

OK so I added

Please Login or Register  to view this content.

and removed ShiftList from the ListFillRange in properties but I am still getting the same issue. When I change the value in the combobox to 1,2,3.... the rows dissapear one by one (ignoring the filter rule), then they all reappear and this happens over and over. I'm thinking it has to be an issue with the function.

[Norie]

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[SHUTTEHFACE]

Please see the attached file

demo3.xlsm

[Norie]

That code is not what I'm suggesting, with that you'll still have the link between the range and combobox.

This is what I meant.

Please Login or Register  to view this content.

[SHUTTEHFACE]

Using that code gives me a range of worksheet failed error.

[Norie]

Add a worksheet reference in front of Range("ShiftList").

By the way, I'm a little confused as the workbook you uploaded doesn't seem to have any comboboxes on the worksheets, the only ones I can find are on userforms.

[SHUTTEHFACE]

There should be one in column H of the PSScheduler sheet.

[SHUTTEHFACE]

Please Login or Register  to view this content.

"Permission Denied"

[Norie]

Here's the code for the sheet's activate event,

Please Login or Register  to view this content.

and for the combobox's change event.

Please Login or Register  to view this content.

[SHUTTEHFACE]

Thanks this seems to be working a lot better as it is actually filtering but is still re-looping over and over.

[Norie]

That's not happening for me.

Have you tried putting some breakpoints (F9) in the code and then stepping through with F8?

That might help pinpoint the problem, I would look out in particular for any subs/functions that are being triggered by hiding/unhiding rows.

[SHUTTEHFACE]

Yep it works, thanks for all your help!