Calc Macro Basic get selected Shape

Lectori Salutem,

I have a Calc Sheet and create shapes on it by copying an existing shape, then modifying it by changing the name, the size, the position, the text on it etc. All works well, but… the shape also has a macro assigned to it to fire an action once the shape is clicked. The macro starts correctly. But I want to know which shape (preferable the name I gave it) started the macro.

I’ve read the Basic Guide 3.2 and the OOME 3.0 but they didn’t bring me any closer to the solution. The code I’ve found in this topic doesn’t help me either.

Sub Snippet2
  Dim oCurrentSelection As Variant
  Dim oObj1 As Variant
  oCurrentSelection = ThisComponent.getCurrentSelection()
  oObj1 = oCurrentSelection.getByIndex(0)
  MsgBox oObj1.Name
End Sub

Maybe because it’s for Draw (and thus on the DrawPage automatically?) but in Calc used as-is I get:

BASIC runtime error.
Property or method not found: getByIndex.

When I change the code slightly to

Sub Snippet2
  Dim oCurrentSelection As Variant
  Dim oObj1 As Variant
  oCurrentSelection = ThisComponent.getCurrentSelection()
  oObj1 = oCurrentSelection.ImplementationName
  MsgBox oObj1
End Sub

I get to see either ScCellObj or ScCellRangeObj depending on what I had selected before I clicked the shape. Only when I right click the shape, then click another shape to fire the macro I see “”. But not from the shape I clicked because the shape I clicked isn’t really selected, it’s only clicked. Unlike a button which gets selected as soon as you click it.

So the question is, it there a way to find out which shape fired the macro? Does the DrawPage have a .getCurrentSelection? Is there another way to do this?

It would be great if I could send a parameter in my macro but there’s no place to define that. I can only select the macro, even if it has an argument

Sub Snippet2(oShape as object)
  MsgBox oShape.Name
End Sub

I get an error:

Message: wrong number of parameters!

because the oShape isn’t set. Any push in the right direction is much appreciated and thanks in advance for your time.

The “Assign Macro…” doesn’t offer an option to pass arguments.

This may not be quiite as easy as you think.
IMO the item Assign Macro... from the context menu of a shape is of little value, and can’t help to achieve what you want.
Study the attached example. The code specialized to your issue is very simple. However, I also included a “full-grown” tool of mine for the extraction of an argument from a query string. Of course this could also be done in the special case with much simpler code, but I don’t want to write special code of the kind for every next task.
disask88305shapeTellYourNameDemo.ods (17.8 KB)

OMG! This is so simple, yet so clever. No more need for an assigned macro. The hyperlink gets set with the creation of the shape anyway so no bother there and I disabled the CTRL so the shape becomes a button instantly without the risk of selecting it and moving it. Fantastic. Thanks!

I do not share your exuberance.
After all, the accepted solution still does not contain an identification of the object on which the link was located. Names of shapes are not unique, and searching for a shape object is cumbersome even if you know an identifying property.
Disabling the “Ctrl+” condition I think is a security issue.
The remark that the shape “becomes a button” this way irritates me. If you want the functionality of buttons, maybe you should use (OK-)Buttons. They have a .Tag property well suitable for passing arguments, and editable without additional user code…

[Translated from German with the help of (free version).]

I must agree with you. As a solution in general it lacks a lot. My enthusiasm came more from simplicity of the solution and not the fact that it has to be done this way. For my application it’s perfect. The names of the shapes are generated by the application so in this case they will be unique. And I know how to deal with them as soon as I have an identifier (which I now have) even if it’s not the shape itself, it’s more for the data behind it that the shape represents.
The security issue is very limited since the application will not be public I didn’t like the fact that the shape became ‘selectable’ with the left click (and thus editable) and needed to be CTRL-Clicked which you don’t have when you assign a macro. I’ve looked into the button option, because that was the effect I was after but buttons are not as customizable and flexible as shapes. I’m sure that’s caused by my lack of expertise with /knowledge of them. Anyways, your solution is a big step forward for me.