[Solved ] Adding a button to mimic Data > Sort keystrokes

[FlyingFish]

I have a spreadsheet with a table of information, set up with an autofilter. Various things the user can do will alter the values inside the table. I'd like to put a button on the spreadsheet outside the area of the table which would exactly mimic the Data > Sort menu choices, with an "enter" at the end, so that the last Sort is repeated. For instance, if the value in the Score column, sorted ascending, was the first sort key, and then Height, sorted descending, was the second key, then that exact sort will be performed again. The "Score" values may have changed based on things the user changed outside the table (i.e. coefficients that determine how each row's score is calculated), so the RESULT of the new sort may not be the same, but the sort KEYS will be. Sending the keystrokes {Alt-D, S, Enter} would almost do (if that's possible -- don't know), other than the fact that if the last cell selected was outside the autofiltered area, things would fall apart. And since there are enough things outside the table that might have been clicked on recently, that's a very real possibility.

The size of the table won't change... always the same number of rows & columns (although some rows may be filtered out & hidden, perhaps), so the range of the table can be hard-coded rather than needing to be determined dynamically.

I'm slightly familiar with how Excel VBA macros work, but this is my first attempt at using Open Office macros. Can someone help me with what the macro should be?

Many Thanks!

Flying Fish

For illustration, a simple table in A3:D6 that looks like this will capture everything essential, I think:

Score -- Height -- Weight -- Age
107 -- 68 -- 140 -- 27
605 -- 70 -- 180 -- 34
202 -- 63 -- 150 -- 40

[FJCC]

I'm usually a proponent of writing macros instead of recording them, but this seems like a good candidate for a recorded macro. You can take advantage of Calc's use of Database Ranges to store the sort parameters to build a very simple macro.
1. Select all of the cells that make up your data table, including the column headers and go to Data -> Define Range. Give the range a name and click OK.
2. With the data table still selected, perform the sort you need. Calc will remember the sorting conditions for the Data Range after you do this.
3. Select any cell outside of the table
4. Select Tools -> Macros -> Record. Click on any cell in the data table and then ALT-D, S (You'll see that the Sort conditions are already set up.) and OK
5. Click on the Stop Recording button and save the macro within the document.

You can now set up a button, key combination, menu item or tool bar icon to trigger this macro. There is a tutorial here that explains how to set up a key combination, menus and tool bars.

[FlyingFish]

Thanks for the reply!

My playing around before I'd posted had missed defining the range, so this works much better than my poor efforts earlier.

However, what I'm looking for is slightly different: If the user last set up his sort on Age then Height then Score, I want to repeat that sort. If he last set it up as Score then Age then Weight, THAT is the sort I want to repeat. Whatever HE last set up, that's what I want the button to launch. (He probably changed coefficients since his last sorting, so his results will be different, but whatever sort keys he used last, those are what I want the button to give him again now.) As described in the last post, the button always launches the sort with the particular sort keys (say, Score then Height then Age) that were in MY last sort when I recorded the macro, and those won't always be what the user is interested in.

Is there a way to command the sort, without specifying (i.e. changing) the sort keys that are present now? Or, is there a way to determine what sort keys were last used, and put them back in to the command?

Many thanks for your help!

Flying Fish

FWIW, the full text of the macro I recorded is:

rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$4"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(8) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ByRows"
args2(0).Value = true
args2(1).Name = "HasHeader"
args2(1).Value = true
args2(2).Name = "CaseSensitive"
args2(2).Value = false
args2(3).Name = "IncludeAttribs"
args2(3).Value = true
args2(4).Name = "UserDefIndex"
args2(4).Value = 0
args2(5).Name = "Col1"
args2(5).Value = 2
args2(6).Name = "Ascending1"
args2(6).Value = false
args2(7).Name = "Col2"
args2(7).Value = 4
args2(8).Name = "Ascending2"
args2(8).Value = true

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args2())


end sub

[Villeroy]

You don't need any macro at all.
Select the list to be sorted and/or filtered.
menu:Data>Define...
Give a name and specify if there are column labels or not.
Apply the sorting, filtering and/or subtotals.
A named database range remembers all settings related to sorting filtering, subtotals and it's import source.
Edit your range, insert new rows, delete rows and call Data>Refresh (by menu, button or shortcut) while a single cell or the whole db-range is selected.
Data>Refresh recalls the previously applied settings.

All this is far easier to handle in a database.
YOu can create a pseudo-db from a spreadsheet and define a query (virtual table) with a default sort order.

[FlyingFish]

Refresh Range looks to do exactly what I needed. Many thanks!

I have one follow-up question, but it's sufficiently unrelated to this that I'll mark this thread as solved, and post the other question separately.

Bravo!

Flying Fish