Ask Your Question
0

How can I get the coordinates of an active cell?

asked 2017-04-17 15:23:34 +0200

fringe0927 gravatar image

sub main()

Dim Current_Sheet As String
oSheet = ThisComponent.CurrentController.ActiveSheet    
oActiveCell = ThisComponent.getCurrentSelection
Cell_r = oActiveCell.RangeAddress.StartRow
Cell_c = oActiveCell.RangeAddress.StartColumn

end sub

On multi-selected cells, when the selected cells are A1:C3 and the cell C3 is active, then

[RangeAddress] - StartRow: 0, StartColumn: 0, EndRow: 2, EndColumn 2.

But I only want to know the coordinates of the active cell(in this case, "C3") that I clicked first.

How can I get it?

Thank you for your help.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-04-17 16:01:07 +0200

Lupp gravatar image

updated 2017-04-18 13:01:14 +0200

(Editing 2017-04-18) See also this thread.

The "ActiveCell" is not a concept of LibO Calc API. It does exist in a sense as you see if you Ctrl+Select a cell inside a range and then use F2 to enter edit mode. But I do not know a way to get the address without a doubt, or at least without a lot of complications.
The CurrentSelection can either support the
"com.sun.star.sheet.SheetCellRanges" service or the
"com.sun.star.sheet.SheetCellRange" service (regard the difference!).
In the first case you have to either enumerate the single ranges or to use the array properties RangeAddresses and/or ElementNames (together with the Count property). In the second case you got a single range anyway.
However, in no case there is anything assured about the position of something like an ActiveCell with respect to anyone of the ranges. In fact there isn't even assured that the active cell is contained in anyone of the ranges belonging to the CurrentSelection.

Workarounds?
I do not know a reliable one. You may "simply" use the Option VBAsupport 1 and then the as-if-predefined variable ActiveCell like in VBA. This is expected to be inefficient and may come with additional disadventages I don't know because I never use the option myslf. Or you quit your Sub or Function respectively if the CurrentSelection does not support the service "com.sun.star.sheet.SheetCell", and thus restrict your code to work only in cases where a single cell is selected. Even in this case you may hit the wrong cell: Select a single cell as a range and then twice Ctrl+Click another cell. If you now inspect the CurrentSelection you will find that it does not contain the active cell again.
Even a trick Andrew Pitonyak once published is not fully reliable.

Sorry. I don't know better news.

Another trick (only working in Calc documents containing at least 2 sheets) :
Save the CurrentSelection to a variable.
Select something in a different sheet. (The previous selection there may be lost.)
Change back to the original sheet. The former selection will have collapsed and the former active cell wil be the new CurrentSelection.
Calculate whatever you want concerning this cell.
Select the saved selection again if wanted.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-04-17 15:23:34 +0200

Seen: 486 times

Last updated: Apr 18 '17