Calc - cell not ready for input after macro

[Inclement]

I recorded a simple macro, attached to a button, to delete contents from cells. At the end, I want the top cell (B2) selected and ready for user input. What happens in practice:

1. The macro works perfectly to delete cell contents.
2. After it’s finished, B2 looks to be selected.
3. I can type a new number in B2.
4. But then if I press Enter, instead of executing the cell formulas that use B2, and moving the selection down to B3, it doesn’t execute the formulas, and it selects the button instead of B3.

Pressing Enter at this point presses the button repeatedly. In order to fix this, I have to manually click in some other random cell. Then the formulas that involve B2 work, and I’m back into normal data entry mode.

I tried recording the “click elsewhere and back” steps into the macro, but it didn’t help.

Here’s my macro, with comments added. First it selects and clears the contents of two non-contiguous ranges, B2 and B5:B11. All the rest is an attempt to simulate the random clicking: B1, B2, B1, C3, B2 twice again. (This is overly zealous, but doing it once also didn’t work.)

The aim is to end up in B2, in data entry mode.

REM  *****  BASIC  *****


sub clear_reading
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ---Select B2-------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$B$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem -------Delete B2's contents---------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem -----Select another range-------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$B$5:$B$11"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem --------Delete the range's contents-------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ---------Here begins the random clicking simulation---------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$B$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$B$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "$B$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$C$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$B$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())

rem --------I want to end up in B2, in data entry mode-----------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
args10(0).Value = "$B$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args10())


end sub

[JohnSUN]

This is not a macro error, it is the greed of the button - it does not want to return focus to the cells after clicking. Just make it, set this property to NO

By the way, the macro code can be much shorter:

Sub ClrWorkRange
Dim oCurrentController As Variant
Dim oActiveSheet As Variant
	oCurrentController = ThisComponent.getCurrentController()
	oActiveSheet = oCurrentController.getActiveSheet()
	oActiveSheet.getCellRangeByName("B5:B11").clearContents(1023)
	oCurrentController.select(oActiveSheet.getCellByPosition(1, 1))	' Select B2
	oCurrentController.select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")) ' Deselect
End Sub

[Inclement]

Works perfectly now, thank you! (Also thanks for the editing suggestions - much nicer that way. Someday will work on actually learning this macro-writing stuff…)