Calc: Delete button created via macro via macro

[gneiss]

As indicated in the subject, I use a macro to create one or more buttons in a SpreadSheet.
If the user clicks on it, a certain “update” should be carried out in the sheet and the button should then “disappear” again
The button is bound to the sub ‘BtnFunc’.
However, the line “form.removeByName( model.Name )” apparently only deletes the event (after that, the button no longer reacts to a click).
The “representation” remains in the sheet…
So I assume that I somehow have to delete the other two objects (btn & shape).
So far I haven’t found anything about it.
Where (in which container) are they?

I gave the objects extra unique names (btn = cell.AbsoluteName & “_BTN” & shape.Name = cell.AbsoluteName & “_SHAPE”).
Xray shows “…_BTN” as the model.Name, but I can’t find “…_SHAPE”.
How do I get they ?

Public Sub PlaceBtnOnCell( action As String, form As Object, cell As Object, btnTxt As String, Optional backColor As Long, Optional tag As String )  
  Dim btn As Object: btn = ThisComponent.createInstance( "com.sun.star.form.component.CommandButton" )
  Dim shape As Object: shape = ThisComponent.createInstance( "com.sun.star.drawing.ControlShape" )
  Dim script As Object: script = new com.sun.star.script.ScriptEventDescriptor
  
  Dim sheet As Object: sheet = cell.Spreadsheet

  btn.Name = cell.AbsoluteName & "_BTN"
  btn.Label = btnTxt
  btn.FocusOnClick = False
  If IsMissing( tag ) Then tag = cell.AbsoluteName & "_TAG"
  btn.Tag = tag

  shape.Name = cell.AbsoluteName & "_SHAPE"
  shape.Control = btn
  sheet.Drawpage.add( shape )
  shape.Anchor = cell
  shape.Size = GetSizeOfCellRange( cell )
  shape.Position = cell.Position
  shape.SizeProtect = True
  shape.MoveProtect = True
  If IsMissing( backColor ) Then backColor = cell.CellBackColor
  shape.ControlBackground = backColor
  shape.CharColor = cell.CharColor
  shape.CharFontName = cell.CharFontName
  shape.CharHeight = cell.CharHeight
  shape.CharWeight = cell.CharWeight

  script.ListenerType	= "com.sun.star.awt.XActionListener"
  script.EventMethod	= "actionPerformed"
  script.ScriptType		= "StarBasic"
  script.ScriptCode		= action
  
  form.registerScriptEvent( form.count - 1, script )
End Sub

Sub BtnFunc( event As Variant )
  RemoveButton( event.Source.Model )
End Sub

Sub RemoveButton( model As Object )
  'Xray model

  Dim form As Object: form = model.Parent
  ' removes: form.registerScriptEvent( form.count - 1, script )
  'Done in PlaceBtnOnCell
  'form.removeByName( model.Name )	

  ' How to:
  ' get/remove shape ?
  ' get/remove btn ?
End Sub

[erAck: edited to format as code]

[Ratslinger]

Hello,
When posting code please use formatting. See → This is the guide - How to use the Ask site? - #6 by erAck

[gneiss]

Sorry, forgot to check if the inserted BB-Meta’s did work here…

[Lupp]

I probably didn’ study the already posted code thoroughly enough, but I’m sure, it’s not quite as simple.
0. @geiss: Please edit your question and use the tool for preformatted code to make your code readable.

1. FormControl Objects aren’t forced to have unique names. In specific: If you create some of them by Copy/Paste, they all have the same name. Judging from my experience this is a fact. It may be suppose to be a bug, however.
2. The FormControl doesn’t “know” the drawing shape hosting it for the view. I only know a fairly complicated way to get that shepe, and only removing the shape will do what the questioner expects.

You may play with the code contained in the attached document. No connection to any DB assumed. You should expect strange effects, however.
disask81066identifyControlHostingShapeAndRemoveIt.ods (12.1 KB)

Please tell me which ones of my detours are actually superfluous, and in wehat way the action can be simplified. I could get the document as ThisComponent, but the issue of controls not knowing the hosting shape and sheet may need a solution.
@Ratslinger:

1. Did you test your code concerning the usage of the name to identify the shape
2. IWill the control actually be removed automatically from the form if the hosting shape is removed from the drawpage?

[sokol92]

@Lupp, thank you for an interesting and carefully prepared example!
On my LO 7.4.0.3 Win 10, when the second button on Sheet2 is pressed, the button stays on the screen. If you select Sheet1 and then reselect Sheet2, the button disappears.
I would suggest this edition:

' ...  
' theControlForm.removeByIndex(theControlIndex)
 theControlsShape.Visible=False
 drawPg.remove(theControlsShape)

[Lupp]

Thanks for your comments.
My own interest in the topic was very abstract (learning more mainly), and I don’t intend to use the code myself in real life. In fact I doubt if the questioner’s approach is reasonable at all. As far as I can guess the situation, I would prefer to “poison” the control to “tempdeath” by disabling it and making the hosting shape invisible. At a different time it might then easily be reinstated, Sufficiently unambiguous identification of control and shape Might be managed using the control’s tag.

See erxample:
disask81066identifyControlHostingShapeAndPoisonIt.ods (12.5 KB)

What you experienced and hinted to was included with my term “strange effects”. I assume, code of the discusses kind is so rarely tried/used that the wee little bugs in the field can have a long life.

[sokol92]

@Lupp

in wehat way the action can be simplified

Using ExtendedAttributes to define a sheet of a Calc document seems tricky. Maybe so:

sheet=doc.CurrentController.ActiveSheet

Or are there pitfalls?

[Ratslinger]

Hello,

For your question, controls are located on the draw page. They must be searched through and checked for the wanted control using the name of the control. Once found you can delete it. Sample code:

Sub DeleteControl
    Dim Doc As Object
    Dim Shape as Object
    Dim Sheet as Object
    Dim DrawPage as Object
    Dim I as integer
    Doc = ThisComponent
Rem accesses first sheet - modify as necessary
    Sheet = Doc.Sheets.getByIndex(0)
    DrawPage = Sheet.DrawPage
    For i = 0 to DrawPage.Count - 1
        Shape = DrawPage(i)
        If HasUnoInterfaces(Shape, "com.sun.star.drawing.XControlShape") Then
Rem Replace name with that for your control - each control has a unique name
            If Shape.Control.Name = "CONTROL_NAME" Then
                DrawPage.remove(Shape)
                Exit For
            End If
       End If
    Next
End Sub

Edit:
.
Another method to get the controls’ index without using the name → How to access the settings under the 'Events' tab of a form control to get / set the routines? - #6 by Lupp

[gneiss]

OK, that works. I had found Your answer to another question where You show a similar approach.
What bothers me is that I had to traverse the whole tree down even that there must be some information (at least on which sheet the event was fired) ex-tractable from the event.

The API is really confusing, especially when searching for good documentation. There is one for OO that matches (partly), another one that is outdated…

I believe, I still didn’t understood the hierarchy used…
And the GUI may lead to wrong assumptions (there it seams that the form did hold the button)

That’s my point of view right now:
In calc, starting with ThisComponent, we have:

• (a collection) of sheets
• a sheet has a drawpage (which is a collection)
• a drawpage has a form-collection (forms)
• a drawpage may hold a number of controls
• a form may have a model (the button)

When an event sub is called it get’s a “reference”.

• event.Source.Model will be the model as listed above
• Till now it’s not clear to me what “event.Source” is (what I have read in one OO doc is definitely wrong for LO)

Because of the assumed hierarchy, I expect that by “going up” (o.Parent), from the model, I should reach the drawpage… But this seams not be true

[sokol92]

I recommend reading the “4. Forms” section of A. Pitonyak’s book AndrewBase.odt

[gneiss]

Oh, lots of answers. I try to comment part by part…

Just to be clear, I already have a working code (see Code 1 below), but the as You can see at the comment above the first statement of function DeleteControl, I believe that’s not really save.
I will do some more investigations into Lupp’s code and try to port the part(s) that retieves the drawPage via pEvent.Source.AccessibleContext.AccessibleParent.ExtendedAttributes.
That’s the part I was missing. I inspect AccessibleContext via Xray, but didn’t com to the idea that there is such an information

Now to Your answers:

@sokol92:
Thanks for the link. Till now a had an older version (from 2009) as pdf, this one is from 2013, so I hope a bit more correct regarding my current LO Version 6 & 7.

@Lupp:
Fortunately someone else (erAck: ?) already had reformatted my original post (Thanks to whom ever)

I had downloaded You .odt file and tested it. For the first time nothing happens when clicking on the button(s).
I debug the code and found that Your function trygetHostingShape will stop at:

For Each shape In pShapeContainer

When I overwrite this function with the one (Code 1) below it works as expected.
Maybe “For Each” isn’t supported in version 6 of calc ? (I had to test it on my other PC with version 7, but don’t have access to this PC right now.

@Lupp/@Ratslinger:

Did you test your code concerning the usage of the name to identify the shape

As You may see I had done both aproachs (Compare by Name and by EqualUNOobjects), both work

Will the control actually be removed automatically from the form if the hosting shape is removed from the drawpage?

This is definaltly true. If You try to remove the comtrol afterwards You will get a “not found”

#######################
Code 1:
#######################

Sub BtnFunc( ref As Variant )
  ' ... some action(s)...
  ' Now remove the button
  DeleteControl( ref.Source.Model )
End Sub

Sub DeleteControl( model As Object )
  ' I that a valid solution ?
  ' Is there a "better/shorter" way to determine the DrawPage ?
  Dim drawPage	As Object:	drawPage	= ThisComponent.CurrentController.ActiveSheet.DrawPage

  Dim i			As Integer
  For i = 0 to drawPage.Count - 1
    Dim shape	As Object:	shape		= DrawPage( i )
    If HasUnoInterfaces( shape, "com.sun.star.drawing.XControlShape" ) Then
      Dim ctrl As Object:	ctrl		= shape.Control
      If Not IsNull( ctrl ) Then
        If shape.Control.Name = model.Name Then
          drawPage.remove( shape )
          Exit For
        End If
      End If
    End If
  Next
End Sub

#######################
Code 2:
#######################

Function trygetHostingShape( model As Object, drawPage As Object ) As Object
  Dim i			As Integer
  For i = 0 to drawPage.Count - 1
    Dim shape	As Object:	shape		= DrawPage( i )
    If HasUnoInterfaces( shape, "com.sun.star.drawing.XControlShape" ) Then
      Dim ctrl As Object:	ctrl		= shape.Control
      If Not IsNull( ctrl ) Then
        ' Name aproach, not preffered
        ' If shape.Control.Name = model.Name Then
        ' Equal aproach, preffered
        If EqualUNOobjects( shape.Control, model ) Then
          trygetHostingShape = shape
          Exit For
        End If
      End If
    End If
  Next
End Function

[Lupp]

Well, I need to study the field a bit deeper if I find the time.
For the moment I only will tell you two things;

1. I still don’t know a way to safely remove a control by name without accepting the chance to kill a different control having the same name for some strange(?) reason. To remove it by index requires the index, and since the object doesn’t know ithis index the need to find it makes it obsolete to use the For Each obj In container Next obj construct in place of the former index-access based way.
2. There seems not to be an UI based way to get that, but using the createInstance method to get a virgin ControlShape object you can have more than one shape (visible and functional) hosting the same (identical) control. This is, at least, a convincing reason for what a control can’t simply know “the hosting shape”: There may be more than one shapes hosting it.

[gneiss]

OK, I totally agree.

1. Even that in my case I can ensure that the names are unique, the “search” approach may have it’s drawbacks. So Searching for a shape that controls the model is the better way (as shown/used in my ‘trygetHostingShape’).
   Your sentence “makes it obsolete…” seams to be stating just the other way around than expected by Your former comments. Are I am right ?
2. OK, that’s a fact I don’t had taken into account. So, IF there will be a back-reference’ from ctrl to shape it must be a container. And then we have to search for the ‘right’ one anyway. So what we have now seams to be the best way.

[Lupp]

ad 1: Due to my poor English I need to “write a bit around”. Recently some (many) kinds of containers or collections were made eligible for inspection using For Each in Basic. If there also were adaptions concerning the support of services I did not yet research. Code like

u = containerObject.Count - 1
For j = 0 To u
 j_object = containerObject(j)
 <code>
 ...
 <code
Next j

seemed to allow for simplification (just a bit) now in many cases. However, if the index is needed not just for the roaming, but also for subsequent actions, the advantage vanishes.

[gneiss]

OK, now we are at the same point (I had some problems to be precise in english, too. I am german)

[sokol92]

The letter is only used in German.

[gneiss]

Nobody knows this better, My Last Name use it (Neiß) AND it’s Pronunciation is like the english word “nice” (Verry nice, isn’t it :-))

[gneiss]

Just to inform You: I had now modified my code so that it uses Lupp’s approach to get the Sheet/DrawPage. Now everything works as it should.

Here is my final code:

REM action = "[document|application]:<Lib>.<Module>.<Function>": document for Subs/Funcs inside Document-, application for ... inside User-Macros
Public Sub PlaceBtnOnCell( ByVal action As String, ByVal form As Object, ByVal cell As Object, ByVal btnTxt As String, Optional ByVal backColor As Variant, Optional ByVal tag As Variant )  
  Dim btnModel As Object:	btnModel	= ThisComponent.createInstance( "com.sun.star.form.component.CommandButton" )
  Dim ctrlShape As Object:	ctrlShape	= ThisComponent.createInstance( "com.sun.star.drawing.ControlShape" )
  Dim script As Object:		script		= new com.sun.star.script.ScriptEventDescriptor
  
  Dim drawPage As Object:	drawPage	= cell.Spreadsheet.Drawpage

  If IsMissing( tag ) Then tag = cell.AbsoluteName

  With btnModel
    .Name					= cell.AbsoluteName
    .Label					= btnTxt				 
    .FocusOnClick			= False
    .Tag = tag
  End With

  With ctrlShape
    .Name					= cell.AbsoluteName
    .Control				= btnModel
    .Anchor					= cell
    .Size					= GetSizeOfCellRange( cell )
    .Position				= cell.Position
    .SizeProtect			= True
    .MoveProtect			= True
    If Not IsMissing( backColor ) Then
      .ControlBackground		= backColor
    End If
    .CharColor				= cell.CharColor
    .CharFontName			= cell.CharFontName
    .CharHeight				= cell.CharHeight
    .CharWeight				= cell.CharWeight
  End With

  REM It is not placed at Index 0 !! It is places at form.count - 1 ? So it act like "AddCtrlShapeToForm"
  form.insertByIndex( 0, btnModel )
  drawPage.add( ctrlShape )
  If Not EqualUNOobjects( btnModel.Parent, form ) Then
    MsgBox( "Button placed on wrong form" )
  End If
  form = btnModel.Parent
  REM If Not EqualUNOobjects( form.getByIndex( 0 ), ctrlShape.Control ) Then
  REM   MsgBox( "Button placed on wrong Index inside form" )
  REM End If
  Dim idx As Integer: idx = GetFormIndexOfModel( btnModel )
  
  With script
    .ListenerType			= "com.sun.star.awt.XActionListener"
    .EventMethod			= "actionPerformed"
    .ScriptType				= "StarBasic"
    .ScriptCode				= action
  End With
  
  form.registerScriptEvent( idx, script )
End Sub

Public Sub RemoveButtonViaEvent( event As Object )
  Dim model As Object: model = event.Source.Model
  
  Dim drawPage	As Object:	drawPage	= GetDrawPageFromEvent( event )

  Dim i			As Integer
  For i = 0 to drawPage.Count - 1
    Dim shape	As Object:	shape		= DrawPage( i )
    If HasUnoInterfaces( shape, "com.sun.star.drawing.XControlShape" ) Then
      Dim ctrl As Object:	ctrl		= shape.Control
      If Not IsNull( ctrl ) Then
        ' Name aproach, not preffered
        ' If shape.Control.Name = model.Name Then
        ' Equal aproach, preffered
        If EqualUNOobjects( shape.Control, model ) Then
          drawPage.remove( shape )
          Exit For
        End If
      End If
    End If
  Next
End Sub

Public Function GetFormIndexOfModel( model As Object ) As Integer
  GetFormIndexOfModel = -1
  On Local Error Goto Fail
  Dim form As Object: form = model.Parent	' form is the container for the model
  Dim idx As Integer
  For idx = 0 To form.Count - 1
    If EqualUnoObjects( model, form( idx ) ) Then Exit For
  Next idx
  GetFormIndexOfModel = idx
 Fail:
End Function