# Accept cell edits to be able to modify cell

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

edit retag close merge delete

Sort by » oldest newest most voted

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

more