I have a multi-sheet spreadsheet. There is a specific cell (say Sheet3.A4) which contains a number. From the current cell (any other cell in the whole spreadsheet) I want to be able to run a macro which increments the number in Sheet3.A4, then copies the new value to the current cell (which should continue to be the current cell).
def increment_and_get(*_):
doc = XSCRIPTCONTEXT.getDocument()
source = doc.Sheets["Sheet3"]["A4"]
source.Value += 1
doc.CurrentSelection.Value = source.Value
You may need apso.oxt to organize python-scripts
and you need to bind the python-code by →→Tools→→Customize→→Keyboard…
Apologies for not acknowledging this sooner, it certainly looks as if it will be perfect, but one place I need it is in Collabora on Android which is (for me) much more awkward than Linux or Windows. But I will get there eventually. Thankyou again.
The same in Basic, but I add the test if only one cell is selected and the 1st cell isn’t current selection.
Sub increaseAndGet
dim oDoc as object, oSheet as object, oCell as object, oSel as object
oDoc=ThisComponent
oSheet=oDoc.Sheets.getByName("Sheet3")
oCell=oSheet.getCellRangeByName("A4")
oCell.Value=oCell.Value+1 'increment the value
oSel=oDoc.CurrentController.getSelection 'current selection
if oSel.supportsService("com.sun.star.sheet.SheetCell") then 'test if onbly one cell is selected
if oCell.AbsoluteName=oSel.AbsoluteName then '1st cell is same like selection
msgbox "select other cell"
else 'selected cell isn't 1st cell
oSel.Value=oCell.Value 'set value to output cell
end if
else 'more cells are selected
msgbox("Select one cell")
end if
End Sub
Somebody might prefer to not base the choice on the .CurrentSelection, but to work with the cell currently having the focus. This should always be exactly one, even if the CurrentSelection doesn’t consist of cells at all, and the actual focus is on a shape, e.g…
There is a demo concerning this topic using an extra toolbar. I wouldn’t apply the tool to all the cells of everywhere, but restrict it to a subset of cells based on a marker in the name of the CellStyle.
disask83654IncDecAndTheLike.ods (18.6 KB)
1 Like
I love such syntax.
@Lupp: Only small note, there isn’t defined the function parameterValuesFromQueryString in the Sub doStepCounterAndInsertResultToFocusCell in your example ODS.
Oh my. That’s a major mistake.
On the one hand I use that function rather often as a helper to pass parameters to Sub, on the other hand it isn’t exatly in a “final” state.
Anyway, I now added it to the demo “as is”, and I will soon edit the post above, and replace the defective file with a hopefully better one.
Support for the use of modificators with the caller from the toolbar is still not implemented.