Manipulating form controls on a Calc Sheet with Basic

I’m trying to have a macro interact with form controls and cells on a document.
Basically I want a button to uncheck several checkboxes when clicked (among other things) .

Problem is I don’t know how to get access to the checkboxes in the macro editor.

I tried

Dim Dlg As Object


Dlg = CreateUnoDialog(DialogLibraries.Standard.Dialog1)

Except my checkboxes aren’t in this Dialog, they’re on a Sheet. And ThisComponent.getControl or ThisComponent.Sheet(0).getControl don’t work.

I’m doing this with a Basic guide pdf because I don’t really know the language, but I can’t figure the solution.


For example, if you chekbox control in active sheet is “chkOne”, then:

doc = ThisComponent
sheet = doc.CurrentController.ActiveSheet
form = sheet.DrawPage.Forms.getByIndex(0)
chk = form.getByName("chkOne")
MsgBox chk.State

If someone used Python like me, then:

doc = XSCRIPTCONTEXT.getDocument()
form = doc.Sheets['Sheet1'].DrawPage.Forms[0]
chk = form['chkOne']
1 Like

It works, thanks a lot. I couldn’t find any information about the methods you used, only about dialogs like in my initial message.
Do you know where I can find resources about this to learn more ?

Please, mark this question as correct.
We need many hands for complete the wiki:

P.D. I prefer Python, it’s more funny.

Any FormControl is (for the view) hosted by a Shape of type “”.
Shapes are inserted into the responsible DrawPage.
In case of a shape anchored somewhere in the sheet object mySheet this is mySheet.DrawPage. There the control shapes live together with all the other shapes of the sheet.
Each shape you can acces baserd on its index from the drawpage. If it is a ControlShape it has a property .Control

On the other hand each FormControl itself is inserted into a Form which in turn is member of the .Forms property of -yes- the DrawPage. Since in most cases you will only have one automatically created Form. You then can access the controls via mySheet.DrawPage.Forms(0) based on an index again. Now you get the naked control model (without the view-oriented shape) …

Once more: The shape knows about the contained control. The control desn’t know about the shape.

If you want to work with a control based on an event, you need to evaluate the .Source of the event.

1 Like

After a break I didn’t check for answers before I posted.
I didn’t delete my post when I found the answer by @mauricio then because it mentions some different aspects.