[Solved] Jump to cell and make it right most visible cell

[edhannahs]

In a macro
If I have columns D through L displayed and jump to a cell in column M, the display changes such that column M is approximately centered. How can I change it so that column M (or whichever column I jump to) will be the right most column in the view. The best I can do after experimenting is to move one cell at a time left (goleft) far enough to assure M goes off screen and then goright the same number of times. While this works, it seems hokey and is visually distracting. Is there something better? Below is the code I currently have for aligning the column to the right after a cell is selected.

I'd rather not change the displayed columns at all if the new cell is already on screen but maybe that part is a separate question.

Code: Select all

Sub GoRightNow
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
osheet = thiscomponent.currentcontroller.activesheet
cctrl =  thiscomponent.currentcontroller

b = 30
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = false

for i = 1 to b
   dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args1())
next i

ocell2 = thiscomponent.getcurrentselection()
cctrl.select(ocell2)

for i = 1 to b
   dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args1())
next i
ocell2 = thiscomponent.getcurrentselection()
cctrl.select(ocell2)
End sub

Windows 10
Apache Openoffice 4.1.3

[FJCC]

To make column M the first visible column on the left

Code: Select all

osheet = thiscomponent.currentcontroller.activesheet
cctrl =  thiscomponent.currentcontroller
cctrl.FirstVisibleColumn = 12

Columns are numbered from 0, so A=0, B=1 ... M=12. I don't know how you jump to column M, so I don't know how to generalize your code to any column.

[edhannahs]

FJCC, that sounds like the key to the whole thing. You showed me first while I asked for last but now I know about parameter I was able to find startcolumn and endcolumn. Going to try that and post back.

Apologies for posting in wrong community. Would switch if I knew how.

[edhannahs]

Going to the right side is trickier than the left side because there is no LastVisibleColumn property. I thought I could just calculate the needed FirstVisibleColumn but it gets difficult due to hidden columns and varying column widths. So had to come to an iterative solution. The code below seems to work. If the target cell column is already visible, it just goes to that cell. If the target column is left of FirstVisibleColumn, it sets FirstVisibleColumn to that column. If the target column is right of visiblerange.endcolumn, it iteratively increments FirstVisibleColumn until it isn't.

This solves my problem. Thanks for the help.

Code: Select all

sub set_cell_focus(oCell, col_offs)
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")
osheet = thiscomponent.currentcontroller.activesheet
cctrl =  thiscomponent.currentcontroller

nRow = oCell.celladdress.row
nCol = oCell.celladdress.column + col_offs

FirstCol = cctrl.firstvisiblecolumn

do while cctrl.visiblerange.endcolumn < nCol
   cctrl.firstvisiblecolumn = cctrl.firstvisiblecolumn + 1
Loop

if nCol < FirstCol then
   cctrl.firstvisiblecolumn = nCol
endif

oCell2 = osheet.getcellbyposition(ncol, nrow)
cctrl.select(ocell2)
cctrl.frame.containerwindow.setfocus    

end sub