[Calc] Get Anchor for a control from event call

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

[Calc] Get Anchor for a control from event call

Post by Mr.Dandy »

Hello folks,

I try to access to Cell under checkbox or button control.
From document, it's easy

Code: Select all

ThisComponent
	|_ getCurrentController
		|_ ActiveSheet
			|_ getDrawPage
				|_ getByIndex		
And I find Anchor property.
But I search a way directly with the control and its incoming event.
From source, I go down to the Model property but I can't go up on Shape property. :(

Help wanted!
Last edited by Mr.Dandy on Mon Sep 13, 2021 4:53 pm, edited 2 times in total.
OpenOffice 4.1.12 - Windows 10
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Calc] Get Anchor for a shape from event call

Post by Zizi64 »

Please upload your small, ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: [Calc] Get Anchor for a shape from event call

Post by Mr.Dandy »

See attached document
All checkboxes are connected to one single macro.
Attachments
checkbox_event_anchor_cell.ods
(11.28 KiB) Downloaded 264 times
OpenOffice 4.1.12 - Windows 10
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Calc] Get Anchor for a control from event call

Post by Zizi64 »

Why you not use the "Linked cell" property of the CheckBoxes?

Maybe you need rename the checkboxes differently. Now every checkboxes have the name: "Check Box 1". Then you will able to browse the objects by the index an the name on the DrawPage of the sheet.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Calc] Get Anchor for a control from event call

Post by Zizi64 »

I just renamed the checkBoxes to
Check Box 01 ... Check Box 10.
checkbox_event_anchor_cell_Zizi64.ods
(11.78 KiB) Downloaded 265 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: [Calc] Get Anchor for a control from event call

Post by Mr.Dandy »

That does not answer how to get anchor.
Zizi64 wrote:I just renamed the checkBoxes
This document is not mine. I can't modify its content.
OpenOffice 4.1.12 - Windows 10
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: [Calc] Get Anchor for a control from event call

Post by karolus »

Mr.Dandy wrote:That does not answer how to get anchor.
Zizi64 wrote:I just renamed the checkBoxes
This document is not mine. I can't modify its content.
If so, your Question makes absolutly no sense …
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: [Calc] Get Anchor for a control from event call

Post by Mr.Dandy »

karolus wrote:If so, your Question makes absolutly no sense …
No sense? Seems to be a missing API to access XShape from XControlModel
OpenOffice 4.1.12 - Windows 10
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: [Calc] Get Anchor for a control from event call

Post by karolus »

If you cannot modify the doc you dont need hack into it.
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
RoryOF
Moderator
Posts: 34570
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Calc] Get Anchor for a control from event call

Post by RoryOF »

XcontrolModel is specified at
https://www.openoffice.org/api/docs/com ... -xref.html

There is reference to XControlShape.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] Get Anchor for a control from event call

Post by Lupp »

We are talking of Calc.
The control is hosted by a shape, and the shape knows its anchor. The anchor can either be the complete sheet sheet or a single cell.
See: https://ask.libreoffice.org/t/libreoffi ... hape/66378

Since I didn't get an answer showing a shorter (more efficient) way, I wrote:

Code: Select all

Function getShapeByControlName(pShapeContainer As Object, pControlN As String) As Object
On Local Error Goto fail
Dim j As Long, u As Long, j_shape As Object
u = pShapeContainer.Count - 1
For j = 0 To u
  j_shape = pShapeContainer(j)
  If j_shape.Control.Name=pControlN Then Exit For
Next j
If j<=u Then getShapeByControlName = j_shape
fail:
End Function
This doesn't tell the position of the shape, but you can get it from the shape. If you want to identify the cell in whose area the top-left corner of the shape is shown by its position, you can...
Well, you know yourself.

See also: viewtopic.php?f=20&t=106029#p515501
There is an example using the above posted code. (I dissuaded from using it, however.)

You may also need something like (very sloppy here):

Code: Select all

Function getDrawwPageFromControlEnvent(pEvent) As Object
Dim accPar As Object, drPg As Object, extended As String, h As String
On Local Error Goto fail
accPar = pEvent.Source.AccessibleContext.AccessibleParent
extended = accPar.ExtendedAttributes
h = CInt(Split(Split(extended, "page-number:")(1), ";")(0)) - 1
drPg = ThisComponent.Sheets(h).DrawPage
fail:
End Function
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: [Calc] Get Anchor for a control from event call

Post by Mr.Dandy »

RoryOF wrote:There is reference to XControlShape.
If I go on xControlModel through MRI and list SupportedServiceName :

Code: Select all

(000) = com.sun.star.awt.UnoControlModel
(001) = com.sun.star.awt.UnoControlCheckBoxModel
(002) = com.sun.star.form.FormComponent
(003) = com.sun.star.form.FormControlModel
(004) = com.sun.star.form.DataAwareControlModel
(005) = com.sun.star.form.binding.BindableControlModel
(006) = com.sun.star.form.binding.DataAwareControlModel
(007) = com.sun.star.form.binding.ValidatableControlModel
(008) = com.sun.star.form.binding.BindableDataAwareControlModel
(009) = com.sun.star.form.binding.ValidatableBindableControlModel
(010) = com.sun.star.form.component.CheckBox
(011) = com.sun.star.form.component.DatabaseCheckBox
(012) = com.sun.star.form.binding.BindableDatabaseCheckBox
Last edited by Mr.Dandy on Wed Sep 15, 2021 9:05 am, edited 1 time in total.
OpenOffice 4.1.12 - Windows 10
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: [Calc] Get Anchor for a control from event call

Post by Mr.Dandy »

Lupp wrote:Since I didn't get an answer showing a shorter (more efficient) way, I wrote:
Thanks Lupp for your advices.
Indeed, in the ODS document, all checkboxes are the same name.
For me, this seems similar to this bug: https://bz.apache.org/ooo/show_bug.cgi?id=58415
ValueBinding property returns void
Another thread shows how to set BoundCell for a control:
viewtopic.php?f=20&t=57575&p=254066

At this time, it is not possible to attach an anchor to its control :(
OpenOffice 4.1.12 - Windows 10
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] Get Anchor for a control from event call

Post by Lupp »

Mr.Dandy wrote:Indeed, in the ODS document, all checkboxes are {have?} the same name.
Indeed it's a strange misconception to have a software where names of objects of the same class generally are forced to be unique, and thus to identify the instances, but to have one class of objects therein where the names are treated as decorations without any meaning or functionality.

However, you can compare instances of he same class for identity (in cases where there isn't a bug doing its worst) using the Basic function EqualUnoObjects(). (See also: https://bugs.documentfoundation.org/sho ... ?id=125421)

Code: Select all

Function getHostingShapeForControl(pShapeContainer As Object, pControl As Object) As Object
On Local Error Goto fail
Dim j As Long, u As Long, j_shape As Object
u = pShapeContainer.Count - 1
For j = 0 To u
  j_shape = pShapeContainer(j)
  If EqualUnoObjects(j_shape.Control, pControl) Then Exit For
Next j
If j<=u Then getHostingShapeForControl = j_shape
fail:
End FunctionNext j
Last edited by Lupp on Wed Sep 15, 2021 10:05 pm, edited 2 times in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] Get Anchor for a control from event call

Post by Villeroy »

The ultimate goal is a transfer of the check box value to the underlying cell.
Attachments
CheckBox.odb
(11.79 KiB) Downloaded 238 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] Get Anchor for a control from event call

Post by Lupp »

Villeroy wrote:The ultimate goal is a transfer of the check box value to the underlying cell.
Yes. That was my suspicion, too, but I don't know where it actually was stated. Was it?

Anyway, this interpretation of the question doesn't make sense. There is the LinkedCell (binding) after all, and -as already stated by "karolus"- with a document I can't modify, I shouldn't tamper.

In the end I decided to take the question literally to make something roughly sensible of it. I had posted a related question "elsewhere", and I dare claim it wasn't just nonsenese. It's a fact that I often focus on certain "principles" or concept instead of answering "pragmatically", and actually it occured that users/questioners appreciated what I offered or suggested. Therefore I felt free to post how I did. On the other hand I failed to see in what way the uploaded .odb was a solution concerning the topic.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] Get Anchor for a control from event call

Post by Villeroy »

Lupp wrote:On the other hand I failed to see in what way the uploaded .odb was a solution concerning the topic.
Every record has a boolean represented by a check box. Clicking a check box writes a boolean value into the "cell" that is defined by a row number and a column name. I created this miracle within 2 or 3 minutes without a single line of code (ok, I wrote the table definition in SQL because it can be done faster this way). It simply does what the spreadsheet fails to do.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] Get Anchor for a control from event call

Post by Lupp »

Yes. And doing it as "Zizi64" suggested in viewtopic.php?f=45&t=106102#p515659 with the orginal sheet would be too fast?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] Get Anchor for a control from event call

Post by Villeroy »

Lupp wrote:Yes. And doing it as "Zizi64" suggested in viewtopic.php?f=45&t=106102#p515659 with the orginal sheet would be too fast?
It does not work as a list and it won't scale. Put some values in column B and click a sort button. The boxes tell the same result for cells in different records.
Try to insert a new record. All this spreadsheet bullshit is not worth the effort. It is just a mock up of a database table.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] Get Anchor for a control from event call

Post by Lupp »

You are right again. We both take an opportunity now and then to stress an aspect not quite clearly being on topic.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Calc] Get Anchor for a control from event call

Post by RPG »

When you understand the interface Interface XTabControllerModel then it is also clear that it is not a bug when all the controls do have the same name. In this case you can easy use it. All the controls with the same name are collected in a group. This group is not the same as what you see on your screen as grouping. But it is important that the group does have an order by indexnumber. In this case it is important that the indexnumber is the same as we need in this case it must be follow the rownumbers.

When we knew this then it is easy starting from the event.
*) Search the control in the group and count the indexnumber
*) calculate the indexnumber for the celladdres
*) do what you want

I have not changed any thing in the spreadsheet. So this means also that the controls are in a good order but I do not know what the reason is that they are in a good order. I want warn people to use this idea in a spreadsheet there I'm afraid that most people do get a wrong result.

Code: Select all

REM  *****  BASIC  *****
option explicit

Sub CheckMe(oEvt)
dim oButton
dim ButtonGroup()
dim obut , x

dim oForm
oButton = oEvt.Source.model 
oForm = oButton.parent
'file:///opt/openoffice4/sdk/docs/common/ref/com/sun/star/awt/XTabControllerModel.html
'http://www.openoffice.org/api/docs/common/ref/com/sun/star/awt/XTabControllerModel.html
'Look for a group
oButton.parent.getgroupbyname(oButton.name,ButtonGroup())
if  UBOUND(ButtonGroup()) then
	' There are several controls for searching 
	x=0 ' Here we set a counter
	for each oBut in ButtonGroup() ' Search for the current button
		if  EqualUnoObjects( oBut, oButton ) then
			thiscomponent.getcurrentcontroller.getactivesheet.getCellByPosition(1,x+1).setvalue(x   +(oBut.state * 1000) )
			exit sub
		else
			x=x+1
	END IF
	next
	PRINT "Done"
else
	msgbox " There is no buttongroup" & chr(13) & "Look in your form"
end if
End Sub
Attachments
checkbox_event_anchor_cell.ods
(11.48 KiB) Downloaded 237 times
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] Get Anchor for a control from event call

Post by Lupp »

Interesting.

However, you surely also noticed that the quasi-identical CheckBox controls all have the same TabOrder, i.e. 0.

Where is the strange informism of a tab order behind the tab order, grouping controls by same name, specified?

In fact. If I change the name (only the number at the end) for a few controls, and test the influence this has on "tabbing-through" I can see the effect expected based on the above explanations. As soon as I change the explicit tab order to any series 0, 1, ... as I want it without touching the names again, this overrides the previous default tab-order. If I then set the changed names back, the original hidden order (Based on -firstly- name, -secondly- order of creation?) is re-established. Where might this behaviour be specified?

If I had developed the "thing" without being bound by a mandatory specification, I would have decided to ignore the tab key if no explicit tab-order is defined.
Last edited by Lupp on Thu Sep 16, 2021 12:34 pm, edited 1 time in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Calc] Get Anchor for a control from event call

Post by RPG »

@Lupp
You asked things to me where I have no real answer for. But this idea is standard used when you use the wizard for GroupBox in a form. I have learned to use it by carefully study all the interfaces for the formmodel. You have real good question but I have not the answers but I believe the tab-order is the same as the indexorder in the group. It can be that it first follow the normal tab-order and when it reach the group it works trough the group and continue with the normal tab-order.

From a little test I see that when I change the taborder that also change the combination with the cell. So the warning I did give is real. But I hope you can understand that it is also important to understand how all things are working and do not assume to early bugs. The idea of bugs was not from you.
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] Get Anchor for a control from event call

Post by Lupp »

BTW: OptionButton objects grouped explicitly by a chosen GroupName don't have a tab-order even if TabStop is set True, and tab-order is set differently for all the controls in the sheet.

Coming from an ungrouped control, strangely the selected OptionButton gets the next TabStop (tested with LibO V 7.2.0.4). How should someone make use of this behaviour?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Calc] Get Anchor for a control from event call

Post by RPG »

To make clear the idea of grouping where I do talk about is not related to the thing we normaly use with form control or in a Calc document. We mean here :
a) We give the controls the same name then OpenOffice makes them to a group.
b) we use the setgroup for controls with different names and set them in a group. Then this controls do get the same name
It is maybe possible that LibreOffice does have change a little special in calc there you can better work with this checkboxes but I do not know it. I want only tell about what is possible with group in a form

Code: Select all

REM  *****  BASIC  *****
option explicit

Sub LearnGroups
dim oForm
oForm = thiscomponent.drawpage.forms.getbyname("Formulier")

oForm.setGroup( _
		array(oForm.getbyname("Tekstvak 1"), _
		oForm.getbyname("Keuzevak 1"), _
		oForm.getbyname("Knop 1")), "ExampleGroup")

End Sub
Attachments
ExampleGroup03.odt
(10.91 KiB) Downloaded 248 times
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] Get Anchor for a control from event call

Post by Villeroy »

If anybody wonders about the benefit of such a tab controller group: It makes equally named option buttons (aka radio buttons) exclusive to each other. The tab key selects the first option button, arrow keys select items within the option group and the next tab selects the next group or single control.
Example: http://forum.openoffice.org/en/forum/do ... hp?id=2933 where the "Type" field (0, 1 or 2) can be filled out by text (listbox), 3 option buttons or a numeric control.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] Get Anchor for a control from event call

Post by Lupp »

The subject of the thread was probably somehow misleading, and the discussion sneaked off in addition.

However, I just posted an answer to my own very similar question from 2021-07-24 in a different site. You can find it here:
https://ask.libreoffice.org/t/libreoffi ... pe/66378/5
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply