Move or resize command button created programmatically on spreadsheet

Good evening everyone,

I’ve been struggling with this for several hours, and cannot seem to find a solution anywhere. Once a command button is created programmatically on a spreadsheet, how do you get to its position or size parameters? API shows them being a command button model property, but browsing through the properties of the button in the watch window, I cannot find anywhere access to it.

Thank you once again for any suggestions.


Sorry but that is a bit funny. There are items I struggled with for months and even years!
Aside from that, the controls are on the drawpage. You need to find them and manipulate. You need to start to understand the MVD paradigm - Model, View and Draw.

For some direction please see my answer here → Manipulating an Image Button in a Calc Sheet.

Now that I look at it, I’m laughing at myself as well. My sincere apologies, but I did not even think about what I was writing. I have simply assumed that I’m missing something so minute, so trivial, that I was actually embarrassed to even post this question. Turns out I stumbled upon something bigger than I thought.

I actually came across your post about an hour before I posted this question, but did not put the two and two together. I guess late hour got the better of me. Nevertheless, this was keeping me up, and here I am at it again. I will definitely go through that entire post and whatever you’re pointing to in it.


Be sure to try the code provided here. It will re-size and move a push button named Button_1 located on first sheet of Calc file.


Here is a routine I had & again tested to resize & move a push button on a Calc sheet:

Sub MoveControl
    Dim Doc As Object
    Dim Shape as Object
    Dim Sheet as Object
    Dim DrawPage as Object
    Dim I as integer
    Dim aPoint
    Dim aSize
    Doc = ThisComponent
    Sheet = Doc.Sheets.getByIndex(0)
    DrawPage = Sheet.DrawPage
    For i = 0 to DrawPage.Count - 1
        Shape = DrawPage(i)
        If HasUnoInterfaces(Shape, "") Then
            If Shape.Control.Name = "Button_1" Then
                aPoint = CreateUnoStruct("")
                aSize = CreateUnoStruct("")
                aPoint.X = 2500
                aPoint.Y = 1000
                aSize.Width = 3500
                aSize.Height = 2000
                Exit For
            End If
       End If
End Sub

Now I see what was throwing me off. When learning a new concept, I always try to minimize everything to bare bones, and then build on it. Unfortunately, in this case it took me a wrong path. I was creating a form and then adding a button do that form, not to a drawpage. When I looked at the drawpage count property just now, it turns out it’s 0. However, what is interesting is that when I created the form and button, it was showing up in the navigator. But when I selected properties of the button, it did not have any way of manually entering the size or the position. The values were completely omitted.

Const SheetNumber = 4
Const FormName = "TestForm"
Const ButtonName = "btnTestButton"

Dim oForms as Object

oForms = ThisComponent.Sheets.getByIndex(SheetNumber).DrawPage.Forms

With oForms

	.insertByName(FormName, CreateUnoService(""))
	.getByName(FormName).insertByName(ButtonName, CreateUnoService(""))
End with

With oForms
End With

I wonder if there is a way of still utilizing my code somehow and getting that button to move/resize. I see there is a method called setParent. I did not make any attempt of trying it yet, but it states that it changes the parent of the object.

I don’t see any point in this as I can’t even see the button. It’s in the Navigator but not on the drawpage where it must reside to be manipulated.

Absolutely true, thank you once again. Enough of this digression :). Hopefully I’ll be able to hit the books tomorrow after work. I just got home not too long ago and I need to get some rest. I wouldn’t be able to concentrate on reading anything anyways at the moment.