I have a basic macro that creates a button on a calc sheet. When the button is created it's anchor is set to cell. When the spreadsheet is saved, closed & re-opened the anchor changes to page, what am I doing wrong?

[MikeZ]

This is the function creating the button:

Function ButtonAdd(strName as String, lngRow as Long, lngCol as Long) as Boolean
  dim objBttn as Object:     objBttn = ThisComponent.createInstance("com.sun.star.form.component.CommandButton")
  dim objShape as Object:    objShape = ThisComponent.createInstance("com.sun.star.drawing.ControlShape")
  dim strLstnr as String:    strLstnr = "com.sun.star.awt.XItemListener"
  dim objScript as Object:   objScript = new com.sun.star.script.ScriptEventDescriptor
  dim objSheet as Object:    objSheet = ThisComponent.Sheets.getByName("App")
  dim objForm as Object:     objForm = objSheet.Drawpage.Forms.getByName("App")
  dim objCell as Object:     objCell = objSheet.getCellByPosition(lngCol, lngRow)
  dim lngCellWidth as Long:  objSheet.Columns(lngCol)
  dim lngCellHeight as Long: objSheet.Rows(lngRow)

  ButtonAdd = False

  objBttn.Name = strName
  objBttn.Label = "Detail"
  objBttn.FocusOnClick = False

  objShape.setSize(objCell.Size)
  objShape.setPosition(objCell.Position)
  objShape.setName(strName)
  objShape.SizeProtect = False
  objShape.MoveProtect = False
  objShape.setControl(objBttn)

  objSheet.Drawpage.add(objShape)
  objShape.Anchor = objCell           'this should set the anchor to the cell

  objScript.ListenerType = "com.sun.star.awt.XActionListener"
  objScript.EventMethod = "actionPerformed"
  objScript.ScriptType = "StarBasic"
  objScript.ScriptCode = "document:Standard.Process.TestExecute"

  objForm.registerScriptEvent(objForm.count-1, objScript)

  ButtonAdd = True

End Function

[Lupp]

The code as posted was slightly garbled (9th and 10th logical line).
In place of the 7th logical line I would suggest objForm = objBttn.Parent immediately above the single line where objForm is used. This is simpler and clearer. A sheet not yet containing a control will also have DrawPage.Forms.Count = 0. You will therefore get an error when trying to accfess a form your way in such a case.
Concerning the anchoring I can only suspect a bug.
You may report it to https://bugs.documentfoundation.org

[MikeZ]

Hi Lupp, thanks for your suggestion regarding DrawPage.Forms.Count I have implemented that in my macro and it works well. The form always has other buttons in it where this is implemented so, I have never seen an error from it.

Sorry about the garbled code, as you can probably tell I took that code from a larger macro and tried to strip out all the unnecessary stuff, plus I’ve been using it for just testing purposes.

What I would really like to do is set the anchor to be “To Cell (resize with Cell)” rather than just “To Cell” but I can’t figure that one out either.

Thanks very much for your time, I appreciate it.

Mike.

[MikeZ]

Turns out, it’s the order of the code. In my original code I had created the shape, set it’s size & position to the cell size & position, added the shape to the sheet’s drawpage and then set the shape’s anchor to the cell.

The correct order is to add the shape to the sheet’s drawpage, anchor the shape to the cell, then set the shape’s size & position to the cell’s size & position, as follows:

objShape = ThisComponent.createInstance("com.sun.star.drawing.ControlShape")
objShape.setName(strName)
objShape.setControl(objBttn)
objSheet.Drawpage.add(objShape)
objShape.Anchor = objCell
objShape.setSize(objCell.Size)
objShape.setPosition(objCell.Position)

Now the anchor is correctly saved when the spreadsheet is closed so that when it’s re-opened it is still anchored to the cell.