Rows not moving

[wsxian]

FIXED-sorta

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 5.2.6.2 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.

[Lupp]

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]

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.'

ThisComponent.CurrentController.Select(theRange)

'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("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
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.

[PYS]

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

Regards

[Lupp]

@PYS: 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.

[PYS]

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

[Lupp]

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.

[wsxian]

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.

[PYS]

Hi

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)
oCursor.expandToEntireRows
thiscomponent.currentController.select(oCursor)
thiscomponent.currentController.setFirstVisibleRow(rowx)

End Sub

Nota: you can comment the expandToEntireRows according to your needs

Regards