Form Control Oddity With Respect To Clearing Filters

[XOR LX]

Hi all.

Was wondering if anyone could explain and/or hopefully resolve this issue for me.

I have a simple table, one column of which contains Form Controls (Option or "Radio" Buttons, to be precise), one for each row in the table, all of them having their property set to "Move and Size with Cells".

This is fine and good, since I can sort the table as desired and these objects remain in their respective rows.

However, I've just noticed an issue re filtering in this table. Filtering a column is fine, as is removing that filter, but only if the latter is done via the Clear Filter option within the drop-down itself. If I instead choose to use the Clear option in the Sort & Filter group within the Data tab, then the radio buttons in any rows which were not part of that filter (i.e. were hidden) are not correctly resized.

For example, try filtering in the attached on column E (Assigned?) for "No". Then see the results of removing the filter the two different ways that I describe.

ExampleXORLX.xlsx

Strangely, any sensible operation will rectify this, e.g. just changing the row height for a single row (it doesn't even have to be changed to anything different - the operation itself is sufficient). Or just simply going into one of the filters and clicking on OK but without actually selecting anything and coming straight back out.

What is happening here? How can it be remedied?

Many thanks for any input.

Regards

[LJMetzger]

Hi Exclusive Or,

I was not able to duplicate the problem using Excel 2003 on a Vista 32 bit system.
I was able to duplicate the problem using 32 bit Excel 2010 on a Windows 8.1 system.

I was able to workaround the problem using VBA as follows:
a. I added a VOLATILE function to the Sheet (such as SUBTOTAL).
b. Whenever AutoFilter is On and turning AutoFilter off would make rows visible, a Worksheet_Calculate() event is generated. I took advantage of the Calculate Event to hide column 'B' and then make column 'B' visible again.

Code in the Sheet Module:

Please Login or Register  to view this content.

Lewis

[XOR LX]

Much appreciated - thanks.

I was hoping that there might be a simple explanation/resolution to this one, but evidently not. Does this mean that this is some sort of "bug" that I've found? What exactly is the technical difference between the two methods of clearing filters anyway?

I'll wait a while to see if anyone can come up with a simple explanation, and if not will implement your solution.

Thanks a lot.

[LJMetzger]

Thanks for the rep points.

Does this mean that this is some sort of "bug" that I've found?

It's not a bug; it's a feature.

What exactly is the technical difference between the two methods of clearing filters anyway?

I don't know what the difference is. This is the first time I've come across a problem like this with Forms Controls.

I've seen it happen quite frequently with 'Active X', especially with respect to hiding rows. The usual symptoms with 'Active X' are either controls with ZERO height, or a height value that is way too large. The 'Active X' fix I've used is to resize the height to the original value. The only way I know to determine the original 'Active X' height is to know what algorithm was used (e.g. Cell Height +/- Delta) was used to create the 'Active X' control.

Lewis

[XOR LX]

Thanks a lot. Interesting.

The only reason I suggested that it might be a bug is that it just seems slightly odd - not to mention inconsistent - that clearing the filter via one method does not lead to this issue yet via another does.

Many thanks again.

Regards