Ask Your Question

Find AutoFilter State from macro

asked 2020-10-25 18:16:48 +0200

wrighch gravatar image

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-10-29 08:16:41 +0200

newbie-02 gravatar image

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 :-(

'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 ...
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-10-25 18:16:48 +0200

Seen: 37 times

Last updated: Oct 29 '20