Find AutoFilter State from macro

In a simple Calc Sheet with AutoFilter enabled, both this question from 2013 and this one that I asked a few days ago in 2020 separately suggest the same answer to the question of how to write a “Macro to Reset an AutoFilter with a Button?” The solution simply proposes two consecutive UNO dispatcher calls:

dispatcher.executeDispatch(document, ".uno:DataFilterAutoFilter", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:DataFilterAutoFilter", "", 0, Array())

to toggle the AutorFilter off, then on again. And it works just fine … unless for some reason the AutoFilter is not enabled to begin with. If AutoFilter is NOT enabled, this solution ends up turning the AutoFilter on, then off again.

My question here: Is there a way, from within macro code, to determine the initial AutoFilter state? If no AutoFilter is enabled, then only a single dispatcher call is needed to (re)enable it. If it IS enabled, then toggling is appropriate to reset it. Any suggestions?

hello @wrighch,

sorry, had a nice long explanation of calc basic macros together for you, including some ranting, ditched a link accidentally and it’s gone :frowning:

‘bugs’ works better in such cases,

thus in short:

[‘calc basic’ | ‘easy intuitive’] - chose !one!,

welcome to the hell of calc basic macro and uno: and API complexity,

don’t think too complicated, it’s easier to switch on than first determine state and then either toggle once or toggle twice (besides structures like that are sometimes useful and are quite common within calc?),

you may try enabling VBA support, just use google (f)or a better search engine, didn’t work for me for this case, but is said to work sometimes,

code in box below contains a solution to switch ‘off’, have fun to find how to switch on, and how to triage between ‘needed’ and garbage in the code …

P.S. ‘solved marks’ and ‘likes’ welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
‘answer’ only if you found a solution yourself …

Option VBASupport 1

sub test_autofilter

ActiveSheet.AutoFilterMode = True

end sub 'test_autofilter

sub test_autofilter_2

'fraction, truncated to autofilter switch, 

' this macro will filter the actual column in a database range 
' with 'autofilter' to the value of the actual cell, while 
' leaving the filtering for the other columns untouched. 
' the only requirement is that you have a 'database range' defined 
' in the table under <data - define range>, and that the 'focus' 
' - the actual cell - is within that range. 

' remaining problems: 
' shorten the code, 
' clear handling of different types of fields, 
' handling of formatted leading zeroes, '00', 
' partly solved - .isnumeric / not
' comment code, 

' when activated in a fresh loaded table where the data-range starts below the first line 
' the filtering is applied as many columns right from the actual cell as the database-range 
' starts below the first row :-( 
' i think that's a bug, circumvention: auto-filter off an re-on, 

' abbreviations: 
oDoc     = ThisComponent
oControl = oDoc.CurrentController
oSheet   = oControl.getActiveSheet
oCell    = oDoc.getCurrentSelection

'variables and values
Row      = oCell.CellAddress.Row
Column   = oCell.CellAddress.Column
Content_v = oCell.value
Content_s = ocell.string
numberformat = ocell.numberformat

'	format = oCell.NumberFormat
' JJ-MM-TT (or YY-MM-DD) is a candidate for errors, it's 'numberformat' is 83
' reformat date strings: 
' turn 
''''	if ocell.numberformat = 30 and len(content_s) = 8 then content_s = "20"&right(content_s,2)&"-"&mid(content_s,4,2)&"-"&left(content_s,2)
' turn dd-mm-yy? 
''''	if ocell.numberformat = 37 and len(content_s) = 8 then content_s = "20"&right(content_s,2)&"-"&mid(content_s,4,2)&"-"&left(content_s,2)
' fill mm-dd: 
''''	if ocell.numberformat = 82 and len(content_s) = 5 then content_s = "2020-"&content_s ' DANGER!!!
' fill yy-mm-dd: 
''''	if ocell.numberformat = 83 and len(content_s) = 8 then content_s = "20"&content_s

' this search is not neccessary in most cases, but keeps us universal
' lookup if the actual cell is within a database range 
oDBRanges = oDoc.DatabaseRanges
for i = 0 to oDBRanges.Count-1
    oDBrange   = oDBRanges.getByIndex(i)
    oCellrange = oDbrange.ReferredCells
    if oCellrange.queryIntersection(oCell.RangeAddress).Count > 0 then
        oRange = oDBrange
    end If
' add a messagebox for errors, 
if isempty(oRange) then msgbox "actual cell not within a database range",0,""
if isempty(oRange) then exit sub
' take actual value as filter criteria
actor() =
' handling of empty cells, they have special functionality in the drop-down-check-boxes, 
' was made with '.empty' by the original author, 
' it looks as if with LO '.equal' and '""' do! work
' 'out-from-ori' if Content = "" then  actor() =
dim oNeu as new
with oNeu
	.Field = Column - oRange.ReferredCells.RangeAddress.StartColumn  ' Filter-Spalte
' normally compare strings
	.IsNumeric = False
'special handling of special cases, should be more general!  
	if ocell.type = 1 then .isnumeric = true
	if ocell.type = 3 and ocell.formularesulttype = 1 then .isnumeric = true
'1,4 .numberformat = 4 
'3/14/13 .numberformat = 30 
'JJ.MM.TT .numberformat = 37
'03-14 .numberformat = 82 
'13-03-14 .numberformat = 83 
'00? if ocell.numberformat = 107 
'03.11.? .numberformat = 109 
'85,25 .numberformat = 118 
'1,25 normaler textstring? .numberformat = 121 
'221,250 .numberformat = 127 
'07 .numberformat = 131 
'0,00 .numberformat = 137 
'1,4 .numberformat = 158 
'11,550 .numberformat = 159 
	.numericvalue = content_v
	.StringValue = Content_s
	.Operator = actor 
end with 'oNeu
' switch on autofilter
oRange.AutoFilter = True
' switch off autofilter
' oRange.AutoFilter = False

'shortened here, full version for surfing is longer 

end sub 'test_autofilter_2