Ask Your Question

Rows not moving

asked 2018-03-23 23:31:35 +0100

wsxian gravatar image

updated 2018-03-26 18:23:30 +0100


I have a macro that reads a UPC code from a barcode reader. I have set up a listener on the cell, and receiving the data, I use the macro to look up the UPC code to get the price and description. I then place the price and description on the same worksheet as the listener cell by row. That seems to work ok, but when I enter more rows than what is showing, the cells do not move up. How can I always show the last row - or make the rows move up so the last row is visible? I have a frozen area at the top (rows 1-11) which contains buttons and information necessary to process the list of items. The inserted rows start at row 12 and go down to as many inputs as are necessary, but only the first 23 show! The user can use the slider bar to see the items pass row 23, but is there some method to show the last row? I tried ThisComponent.getCurrentController().setFirstVisibleRow(rowx), where rowx was the last row, but that did not work. Version running on Anti-X 16.

Ok, I fixed this by placing my listener below the frozen row. Apparently, I have to give the focus to the cell where the listener is and when I do that, the whole window is re-written which means that even if I was able to move the row down, the re-writing of the window sets it back to where it was.

This is a Klunky fix, and instead of one cell as a listener, I am now using every row (in one column) as a listener. It works, but not as clean as having just one cell above the frozen rows.

edit retag flag offensive close merge delete


Quoting @wsxian; "..., I have to give the focus to the cell where the listener is and when I do that, ... which means that even if I was able to move the row down, the re-writing of the window sets it back to where it was."
Did you actually try the example I attached to my answer by editing? It demonstrates how to avoid this. Just tried it again myself successfully.
Little stain: I don't know a way to move the focus this way withoout using an uno-command.with the dispatch helper.

Lupp gravatar imageLupp ( 2018-03-26 19:53:34 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-03-24 11:57:00 +0100

pierre-yves samyn gravatar image

updated 2018-03-24 12:04:13 +0100


The setFirstVisibleRow command may not work if the cursor is for example in the frozen window.

So another proposal:

Sub PysSelectRow
dim oCursor as object, oSheet as object, oCell as object
dim rowx as long

rowx = 2310

oSheet = thiscomponent.sheets.getByName("Sheet1")
oCell = oSheet.getCellByPosition(0, rowx)
oCursor = oSheet.createCursorByRange(oCell)

End Sub

Nota: you can comment the expandToEntireRows according to your needs


edit flag offensive delete link more

answered 2018-03-24 00:03:41 +0100

Lupp gravatar image

updated 2018-03-25 01:11:30 +0100

A very raw attempt:

Sub demo()
'freeze a range of rows/columns containing cell A1.'
theRange = ThisComponent.Sheets(0).GetCellRangeByPosition(90,999,999,999)

'Only for the demo. Actually the range / cell you want to have in the view.'


'Let's assume the cell you want to get the focus is A1.'
'You need to go there without selecting the cell.'
'I do not know a way using the API, but the you can do it with'
'an uno command using the dispatcher.'
'From a recorded macro:'

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("")
dim args1(0) as new
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

'If A1 is in the frozen range, the previously selected range should '
'still be in the view.'

End Sub

Edit1 with regard to the recent comment by the OQ:
I didn't just tell a story. You may play with this attached demo. Of course, my input doesn't come from a barcode scanner. A1 having the focus and getting input from elsewhere should, however, work as well.

edit flag offensive delete link more


Hi Lupp, my update was only an attempt to improve the presentation of comments, but it does not work, really sorry


pierre-yves samyn gravatar imagepierre-yves samyn ( 2018-03-24 12:01:23 +0100 )edit

@pierre-yves samyn: You are always welcome!
What would you think about a suggestion to organise the storage stack-like: Insertion always in the same row pushing rows with old content down (insert mode 3)? After all a spreadsheet isn't a database table, but directly visible to the user.

Lupp gravatar imageLupp ( 2018-03-24 13:24:54 +0100 )edit

@Lupp - not sure I understand: XTableRows interface provides methods to insert/delete rows via index

pierre-yves samyn gravatar imagepierre-yves samyn ( 2018-03-24 13:33:14 +0100 )edit

I (internally) referred to XCellRangeMovement.
My "scenario" was: One cell has the focus and gets input from a barcode scanner. Ordinary formulas do the calculations supplying a new dataset. Additional measures triggered by the listener (or a sheet event) may be taken by user code. The new dataset is then to copy to its primary storage location. This can be always the same place if older datasets are moved down: "Stack-like". If the process is attended the view should accommodate the user.

Lupp gravatar imageLupp ( 2018-03-24 14:08:42 +0100 )edit

I tried both answers and neither worked. I tried putting them together and that did not work either. It would be very easy to just insert a carriage return in the row after rowx using a macro (I went to the row after row 22 and inserted a return and the rows moved up so that works, but I don't want my user to have to insert a return or use the scroll bar to move the rows), but I don't know how to do that or even if that's possible.

wsxian gravatar imagewsxian ( 2018-03-24 23:16:02 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-23 23:31:35 +0100

Seen: 91 times

Last updated: Mar 26 '18