Ask Your Question

Go to cell without losing existing selection

asked 2019-02-15 17:20:41 +0200

Hynerian gravatar image

updated 2019-02-15 20:12:16 +0200

Context: I am helping the reporter of this RFE by trying to create a macro for keyboard-assisted multiselection of cells.

Here is what I have put together based on the example in Andrew Pitonyak's Macro book's section 6.5.2

Sub RetrieveTheActiveCell()
  Dim oOldSelection 'The original selection of cell ranges
  Dim oRanges        'A blank range created by the document
  Dim oActiveCell   'The current active cell

  REM store the current selection
  oOldSelection = ThisComponent.CurrentSelection

  oRanges = ThisComponent.createInstance("")
  'get the active cell!
  oActiveCell = ThisComponent.CurrentSelection

  'add new selection to the old selection
  oOldSelection.addRangeAddress(oActiveCell.getRangeAddress, False)
End Sub

This works in Calc's "Adding" selection mode, but it would be great, if the cell focus did not jump to the top of the selection.

If I use uno:GoToCell at the end, it loses the selection.

Is there any way to preserve the focus in the original active cell?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-02-15 17:56:55 +0200

Lupp gravatar image

updated 2019-02-15 18:23:25 +0200

I don't understand the "go to" contained in the subject.
The question looks as if it is about getting the cell having currently the focus, but not knowing a way to do so without collapsing the current slection first.
If so, it's simple: Dont't select an uninitialized SheetCellRanges object to get the actually unselected focus as a fake selection. Use the following code instead to get the focus cell whether it is currently selected itself or not:

Function focusCell(Optional pCurrCtrl) As Object
REM Concept by "uros", "Villeroy"
REM Responsible for this variant: Wolfgang Jäger
REM 2017-09-28 V0
If IsMissing(pCurrCtrl) Then pCurrCtrl = ThisComponent.CurrentController
If  NOT pCurrCtrl.SupportsService("") Then Exit Function
    Dim theSheet As Object, fC As Object, sheetNum As Long, sInfo As String, sInfoDelim As String 
    Dim vD, vDSplit, sInfoSplit
vD             = pCurrCtrl.ViewData
vDSplit        = Split(vD, ";")
theSheet       = pCurrCtrl.ActiveSheet
sheetNum       = theSheet.RangeAddress.Sheet
sInfo          = vDSplit(sheetNum + 3)
REM For CellAddress.Row >= 8192 the "+" is used as the subdelimiter in ViewData. 
REM WHY? Donno!
If InStr(sInfo, "+")>0 Then 
    sInfoDelim = "+"
    sInfoDelim = "/"
End If
sInfoSplit     = Split(sInfo, sInfoDelim)
fC             = theSheet.GetCellByPosition(sInfoSplit(0), sInfoSplit(1))
focusCell      = fC
End Function

See also:

Well, since I read the question once more, I want to ask in return: Why do you want to use the "GoToCell"? Do you want to select the focus cell if it was not selected at the beginning? Do you need to do something else intermediately, and to select seomething else for the purpose for a while?

edit flag offensive delete link more


I don't particularly want to use "GoToCell", I just mentioned it as an example of something that does not work the way I want.

I can describe the desired workflow of the imagined macro in action.

  1. Assign macro to some shortcut key
  2. Change selection mode to Adding
  3. Hit shortcut key to select a cell
  4. Move with the arrow keys to anywhere in the sheet
  5. Hit shortcut key to add a new cell to the existing selection repeat 3.-5. ad infinitum

The macro I posted does what I want except it changes the focus to the top of the selection, thus making it very impractical.

I tried with your function at step 5., but it results in the whole sheet getting selected:

oOldSelection = ThisComponent.CurrentSelection
cell = focusCell()
shAddr = cell.Spreadsheet.getRangeAddress()    
oOldSelection.addRangeAddress(shAddr, True)
Hynerian gravatar imageHynerian ( 2019-02-15 20:17:30 +0200 )edit

Sorry. I partly misunderstood the question. (Unprecise reading.)
I don't know a way to set the focus otherwise / independently, or to put it on an unselected cell by user code.
Yoiu can do it interactively via the UI, and there must be a way internally, but it seems to be supported neither by the API nor by the slot-machine of the dispatch helper.

Lupp gravatar imageLupp ( 2019-02-16 01:02:05 +0200 )edit

I created a request for GoToCell to respect selection modes. Feel free to comment on it with your thoughts.

Hynerian gravatar imageHynerian ( 2019-02-16 10:49:47 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-15 17:20:41 +0200

Seen: 70 times

Last updated: Feb 15