Apply boolean autofilter criteria to different language

user_6161587 0
Viewed 2005

I have a table which I filter based on a Boolean value.

Whenever the value is TRUE, I want it to show.

I use an autofilter and the following working VBA code:

lCol = tbl.ListColumns("xFilter").Index
With tbl
    If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
    .Range.AutoFilter Field:=lCol, Criteria1:="TRUE"
End With

I am working on an English instance of Excel. When I try this on an instance in Dutch I have to manually set the Criteria to WAAR (Dutch equivalent of TRUE).

I can add multiple criteria and state:

Criteria1:="WAAR", Operator:=xlOr, Criteria2:="TRUE"

However, if I would then go to Germany and Spain I would need to write:

Criteria1:="WAAR", Operator:=xlOr, Criteria2:="TRUE", Operator:=xlOr, Criteria3:="WAHR", Operator:=xlOr, Criteria4:="VERDADERO"

Is there a way to have Criteria1:="TRUE" work in any language?

autofilter multilingual boolean vba excel
3 Answers

Try this:

Criteria1:=CStr(True)

user_85209950

The other answers did not work for me in Russian Excel, where I was trying to do some coding independent of language. In the end, I had to utilize a set of two cells where I manually put TRUE and FALSE values (ИСТИНА and ЛОЖЬ in Russian) and I reference their .Text (this is important!) instead of .Value2 in the Criteria. It seems like the issue is that Criteria uses .Text from cells in the filter which may not be equal to the text True and False.

This solution is far from perfect, obviously, but, so far it's the only way I've found. It's good that Excel stores boolean values as 0 and 1: if you open the Workbook as a zip file, you can see files like sheet1.xml which will have entries like this for boolean cells:

<c r="B5" s="6" t="b">
<v>0</v>
</c>

t seems to represent the type of the value—in this case b, which implies boolean.

user_29928510
Related