may I recommend install APSO.oxt from here and use it for Management of Python-scripts for Libreoffice.
Additionally … you need to bind the function above to SheetEvent Content changed
(→Right-click on SheetTab→Sheetevents… )
I’ve been out of the practice of coding for over a decade. I’m not good with it anymore and I don’t have a clue how to use those tools. I’ll give it a try but I’m not confident I can get any of it to work. I have no idea how to read a debugger. No clue how to even install it.
Testing your python script I used the code you wrote to replace a recorded macro. It’s throwing a syntax error asking for “then” constantly. I can’t do anything in the document or the code because of the error.
Nope! I’m not able to work with python scrips. Saw the wiki here and that’s way too much digging and editing etc just for one macro. I also don’t have a python editor and have no clue how to write python code at all. If an editor is needed other than a text editor that’s another thing I don’t have.
Thank you very much! I’m not able to use that though.
python is not Basic, why do you expect it works by magic within BasicIDE executed by Basic-Interpreter??
LOL I don’t expect it to work like magic. I gave it a shot the way I know how.
Python’s not for me. I’ve just barely started getting a slight but still baffled beyond confused understanding of Basic.
I had already suggested you to install APSO. It does most of the stuff behind the scene, but in any case you need to write code yourself (either in Basic or Python) or to find some guy who write it for you.
Coding takes forever to learn and even longer to get good at. I’m old. I just want to get this sheet working.
Sub hideRows
dim oDoc as object, oSheet as object, oCell as object, p(), s$, oRange as object, oCur as object, i&, j&, a1(), a2()
rem your data
const cCell="D1" 'name of the decision cell
p=array( _
array("Bohemia", "3:7"), _
array("Canada", "22:25", "30:38", "48:52"), _
array("India", "17:21", "60:61") _
)
oDoc=ThisComponent
rem ********** if you have many formulas in your sheet, then this can be faster
dim bAutomatic as boolean
bAutomatic=oDoc.isAutomaticCalculationEnabled 'value of the: Data/ Calculate/ AutoCalculate
if bAutomatic then oDoc.enableAutomaticCalculation(false) 'deactivate AutoCalculate
oDoc.lockControllers() 'no screen actualization
oDoc.addActionLock() 'lock
oSheet=oDoc.CurrentController.ActiveSheet 'active sheet
oCell=oSheet.getCellRangeByName(cCell) 'decision cell
s=oCell.string 'string from decision cell
rem view all rows
oCur=oSheet.createCursor
oCur.goToEnd(false)
oRange=oSheet.getCellRangeByPosition(0, 0, 0, oCur.RangeAddress.EndRow) 'used rows in column A
oRange.Rows.isVisible=true 'set all rows to visible
rem hide rows
for i=lbound(p) to ubound(p) 'loop all arrays
a1=p(i)
if a1(0)=s then 'array with needy name
for j=1 to ubound(a1) 'loop all ranges in array
a2=split(a1(j), ":")
oRange=oSheet.getCellRangeByPosition(0, CLng(a2(0))-1, 0, CLng(a2(1))-1 ) 'rows in column A
oRange.Rows.isVisible=false 'hide the rows in actual range
next j
exit for 'exit the loop
end if
next i
rem **********
oDoc.removeActionLock() 'no lock
oDoc.unlockControllers() 'screen actualization
oDoc.enableAutomaticCalculation(bAutomatic) 'set AutoCalculate to the original value
End Sub
Example
hideRows.ods (13.8 kB)
Perfect! Words can’t express Thank you enough!
The built-in filters work perfectly well. Instead of playing around you could upload an example and tell us what goes wrong when you apply advanced filtering.
Even if you insist in writing macros for the wrong tool (again: all this is database stuff), you could write a macro that controls the advanced filter like this may be: https://forum.openoffice.org/en/forum/download/file.php?id=136 which makes use of some generic code working with a named criteria range and a named database range. The input goes into a form control which triggers the macro with every character you add or remove.
This 700 MB software is prepared to be usable without writing stupid macro programs. All this works more or less exactly as it used to work in Microsoft Excel since the early 90ies.
Preparing advanced filtering with no macro code.
- Define a database range. This is a special type of named range for database-like lists to be filtered and sorted. It “remembers” the last sort and filter criteria. Again, true database tables are preferable.
- Add a criteria range as descirbed in the help files and in countless example files. The most simple criteria range consists of 2 cells: a column label specifying a distinct column of a database range and one cell below specifying one criterion.
- [Optional] Name the 2 cells and flag the named range as “filter criteria”…
- Call the auto-filter dialog and point to the criteria range. If you executed step 3, you can pick the criteria range from a list box.
- After clicking OK, the database range should be filtered by the = Value pair in the criteria range.
One advantage of “advanced” filters is that it allows calculated filter criteria.
As long as you do not modify the criteria, you can keep on working with the database range. You can add record by row isertion. You can remove records by row deletion, You can edit records. menu:Data>Refresh will refresh the database range with all its currently set sorting and filtering criteria.
After you modified the criteria range: menu:Data>Filter>Advanced Filter (or any shortcut to this command) pops up the criteria dialog with the previously set criteria range. Just click OK or hit Enter. and the new criteria will be applied to the selected database range.
Instead of selecting a whole database range, you can also select any single cell within that range. The selection will expand accordingly.
@Villeroy Filters don’t work in the project file for a vast number of reasons. I asked a very clear, specific, question for a VBA code to be made into basic so it worked with calc. That’s what I wanted. I asked for what I wanted and needed.
If the built-in features do not work for your specific project for multiple unspecific reasons, then you definitively use the wrong software. Stop wasting your time (and ours) “playing around” (your term) with spreadsheets and StarBasic code copied from other people.
1 Like