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 · March 21, 2020, 8:12pm

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 · March 22, 2020, 12:04am

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 · March 30, 2020, 2:40pm

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 · April 8, 2020, 5:51pm

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.

Woof4Grrrr · September 25, 2024, 8:02pm

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.

Did you ever find a way to get your button to resize when the cell’s size changes?

Lupp · September 26, 2024, 12:06pm

By the UI this is supported as long as the contol editor is not activated.

Since the feature ResizeWithCell was introduced (V6.1), it was never a property of the FormContol (OK Button e.g.), but one of the hosting Shape. The UI doesn’t make the appropriate distinction.
It merges settings for the control itself and for the hosting shape on the same level, but the setting “(resize with cell)” wasn’t inserted into the anchor line there.

For the macro:
With recent versions you should find the property listed in alphabetical order when inspecting the shape object in the watch window.