Accept cell edits to be able to modify cell

[jheath]

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:

Select cell to remove text insertion point

Return focus to calc sheet

    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 6.0.6.2, 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.

[jheath]

With a little bit of perseverance with MRI, I was able to answer my own question. I no longer blindly use getAccessibleChild(3) and getAccessibleChild(4) - apparently the indices have changed a bit in this latest version of LO, but I scan through the children, looking for one that has an AccessibleName of “Accept” (or “Accepter”, since I also work in French). That’s the child that I execute a grabFocus and doAccessibleAction on. I also commented out that final line, which was supposed to put the focus back on the document, as it doesn’t seem to be necessary from my testing, and also blindly uses indices, which as we’ve seen is dangerous…

    Sub _jcLeaveEditMode(ByVal oDoc As Object)
        Dim i As Integer
        Dim j As Integer
        Dim oAC As Object
        Dim oACToolbar As Object
        Dim oACAccept 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
            For j = 0 to oACToolbar.AccessibleChildCount() - 1
                oACAccept = oACToolbar.getAccessibleChild(j)
                If oACAccept.AccessibleName = "Accept" Or oACAccept.AccessibleName = "Accepter" Then
                    ' We are in edit mode, attempt to get out of it or else we can't modify the cell
                    oACAccept.grabFocus
                    oACAccept.doAccessibleAction(0)
                    ' Assume that first child is the document, put focus there (don't need this any more?)
                    'oAC.getAccessibleChild(0).AccessibleContext.getAccessibleChild(9).AccessibleContext.grabFocus
                    Exit For
                End If
            Next j
        End If
        On Error Goto 0
        Exit Sub
    SkipFocus:
        On Error Goto 0
    End Sub