[Solved] Identify 'selected' cell in a non-active sheet

Creating a macro - Writing a Script - Using the API

[Solved] Identify 'selected' cell in a non-active sheet

Postby htj6971 » Thu Jan 12, 2017 12:01 am

Have ONE calc document with three (3) sheets, sheet1, sheet2, and sheet3
For the purposes of this question, assume that on every sheet I have selected one cell. I go to sheet2, select B15, then go to sheet3, select C45. If I go back to sheet2, B15 will still be selected. Back to sheet3, C45 will still be selected... etc...

Now my active sheet is sheet1, and I need to be able to identify the already "selected" cells on sheet2 (B15) and sheet3 (C45).

- "GetCurrentSelection" works for on the entire document, and gets me only ** active ** sheet / active cell (sheet1)

- Can't really do "Sheets.getByName(sheetx) .. GetCellByPosition",since I really don't know which cells will be "selected"(If any, or what if more than one cell -- I know that, and I can deal with it) in sheet2 and sheet3

- I'd rather not do "Controller.setActiveSheet(Sheet2) .. GetCurrentSelection", then "Controller.setActiveSheet(Sheet3) .. GetCurrentSelection", and then back to Sheet1

I went through the entire Sheet object structure, and can't seem to find where the selected cell(s) information is stored (Finding such information in the document object was easy - which explains "GetCrrentSelection"), and have not been able to find any calls to give me access to selected cell from a named (other non-active) sheet.

Any guidance will be greatly appreciated or You can just point me to a documentation page, and I'll be happy a a clam.

Thank you for any help you can provide.
Last edited by htj6971 on Thu Jan 12, 2017 8:06 pm, edited 1 time in total.
htj6971
 
Posts: 5
Joined: Wed Jan 11, 2017 11:07 pm

Re: Access 'selected' cell in a non-active sheet - calc basi

Postby Villeroy » Thu Jan 12, 2017 2:47 am

The current selection is either one of:
1. com.sun.star.sheet.SheetCellRange which may include a single cell or a rectangle of cells
2. com.sun.star.sheet.SheetCellRanges which is a collection of multiple ranges
3. an array of selected shapes

When you have more than one sheet selected, the second case will include the active sheet's range(s) selection for each selected sheet.
When you have more than one sheet selected and shapes on the active sheet, only the selected shape of the active sheet is returned.

Regardless of the actual type of selection, there is always one active cell for each view (window) but the API has no direct method to access this cell. You have to analyse a special ViewData string which stores the current view setting of a spreadsheet document.
Code: Select all   Expand viewCollapse view
Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
   as1()  = Split(oView.ViewData, ";")
   lSheet = CLng(as1(1))
   sDum = as1(lSheet +3)
   as1() = Split(sDum, "/")
   on error goto errSlash
      lCol = CLng(as1(0))
      lRow = CLng(as1(1))
   on error goto 0
   getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
   if NOT(bErr) then
      bErr = True
      as1() = Split(sDum, "+")
      resume
   endif
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27381
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Access 'selected' cell in a non-active sheet - calc basi

Postby htj6971 » Thu Jan 12, 2017 7:04 pm

ViewData is exactly what I was looking for. This is where all these pesky variables are stored!

Thank you for your reply.
LibreOffice 5.3.3.2 (x64) on windows 10
htj6971
 
Posts: 5
Joined: Wed Jan 11, 2017 11:07 pm

Re: Access 'selected' cell in a non-active sheet - calc basi

Postby Villeroy » Thu Jan 12, 2017 7:54 pm

You're welcome.
However, the answer to your question "Access 'selected' cell in a non-active sheet" is: you can't
Oh, I think you can. The ViewData string changes when you activate another cell on a non-active sheet.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27381
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Identify 'selected' cell in a non-active sheet

Postby htj6971 » Thu Jan 12, 2017 8:13 pm

I Just saw your amended reply. Correct.

But I changed the subject from "access" to "identify" anyways, because this was my initial intent.

I marked the post as "solved". Thank you again for your help.
LibreOffice 5.3.3.2 (x64) on windows 10
htj6971
 
Posts: 5
Joined: Wed Jan 11, 2017 11:07 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests