I have some finance journals in LibreOffice Calc, and one requirement is that I should be able to type a partial account name in an account cell, press enter, and it will go look up the full account name and place it in the cell. But if I am currently typing in the cell, there is an insertion point in the text, and I can’t actually change the text in that cell.
For several years I have been using the following LibreOffice BASIC code to check to see if we are in Edit Mode, and then (I guess) Accept the edits, and remove the insertion point. See previous discussion at these two links:
Dim i As Integer Dim oAC As Object Dim oACToolbar As Object Dim oACSumCancel As Object Dim oACFunctionAccept As Object ' Check to see if we are in edit mode (i.e. with active cursor in the cell) ' Lots of things could go wrong, set up an error routine On Error Goto SkipFocus oAC = oDoc.CurrentController.Frame.ComponentWindow.AccessibleContext For i = 0 to oAC.AccessibleChildCount() - 1 oACToolbar = oAC.getAccessibleChild(i).AccessibleContext If oACToolbar.AccessibleName = "Formula Tool Bar" Or _ oACToolbar.AccessibleName = "Barre d'outils Formule" Then Exit For End If Next i If i < oAC.AccessibleChildCount() Then oACSumCancel = oACToolbar.getAccessibleChild(3) If oACSumCancel.AccessibleName <> "Sum" And oACSumCancel.AccessibleName <> "Somme" Then ' We are in edit mode, attempt to get out of it or else we can't modify the cell oACFunctionAccept = oACToolbar.getAccessibleChild(4) oACFunctionAccept.grabFocus oACFunctionAccept.doAccessibleAction(0) ' Assume that first child is the document, put focus there oAC.getAccessibleChild(0).AccessibleContext.getAccessibleChild(9).AccessibleContext.grabFocus End If End If On Error Goto 0 Exit Sub SkipFocus: On Error Goto 0
But now that I have upgraded to LibreOffice 184.108.40.206, that code no longer works. If the insertion point is still there, like this:
then when I process an Enter press, I can set the string of the selection, and it seems to “take” (the MsgBox afterwards shows the correct account name that has been looked up):
' Store the full account name back into the cell oSel.setString(sAcctName) ' see if it changed... MsgBox(oSel.getString())
But when focus moves to the next object, the text reverts to the “Heath” text, not the full account name. I assume that’s because the text up in the Formula bar gets “applied” and wipes out the contents of the cell.
So is there an easier/better way to do this in the new LibreOffice? I never did like that code anyway… I need a way to tell Calc (in my Basic macro) that it should “Accept” the edits in the current cell, and allow me to modify the cell.