Excel VBA column-specific autofiltering not processing comma-separated decimals, why and what to do?

[MrDash]

A few days ago I faced a need to create a set of data tables that would allow users to quickly filter the data based on at least 10 datapoints (columns). As an old Excel fanboy I switched to the newest desktop app, part of the 365 package. I'm most familiar with the old 2003 desktop version.

I need to create range filters, and was surprised to learn that Excel really does not have these controls out of the box. Seem to be in their BI offering though. So I created for each of these 10 columns three ActiveX controls: min value textbox, max value textbox and reset button that resets both min and max. The min and max textbox is linked to two cells. Then I wrote the VBA macro that autofilters each column based on the values created from the linked cells' text content and hides the autofiltering arrow of the column. Seven of the columns started autofilter like a charm, but three did not. These had decimal (floating point) numbers, not integers. The data was good, as manual autofiltering worked flawlessly for decimal numbers too. So I started to suspect it has something to do with either decimal character conversions that take place under the Excelhood. Googled a bit and found link to an article (TEXT vs VALUE vs VALUE2 – Slow TEXT and how to avoid it), was running out of time, tried out Range.Value2 and Range.Text insted of Range.Value already in the code, and ended up with a working filtering system, but only after setting Excel's decimal separator to dot (.) and not comma (,) which we use here.

Now with slightly easier schedule I'd need to find how to make autofiltering process comma decimal separated data correctly. Can someone please help?

Any and all help is greatly appreciated!

Thanks in advance.

 

[Candyman8019]

I wonder if you were to set the decimal separator in the options menu to a comma rather than decimal.

 

[MrDash]

I wonder if you were to set the decimal separator in the options menu to a comma rather than decimal.
View attachment 64607

Thanks for helping out. I chose File - Options - Advanced, unselected Use system separators, and right under the option set Decimal separator as .
I did not do anything to the unselected Automatically insert a decimal point.