Excel & VB - Retrieve the name of the clicked object

Sub ShapeClick()

Dim ShapeName As String
ShapeName = Application.Caller

Msgbox ShapeName

End Sub

You assign the macro to the desired objects

Thanks to ChrisKang in another discussion!

Hello,

I see that you're still struggling with your colored rectangles.

As we’ve already told you, sometimes all it takes is to record a macro for the solution to become clear.
I’m going to explain how I do it.

For example, I draw a rectangle "Rectangle 1", then I record a macro "Macro1" (Tools Menu Macro New Macro)
Right-click on the rectangle, Format AutoShape...
I change the background color, the border... I close the dialog box.
I stop recording Macro1, Visual Basic Editor, Module1 (or Module2...), and I retrieve the code

Sub Macro1()
'
' Macro1 Macro
' Macro recorded on 02/18/2008
'
'
ActiveSheet.Shapes("Rectangle1").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 11
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
End Sub

It’s not complicated. Now I want to display certain properties of this rectangle when I click on it.

I create a macro called Rectangle1_WhenClick(), be careful with the spelling, the name is important because it refers to a predefined event.

Sub Rectangle1_WhenClick()
'to answer your question I'm displaying the name
'but I don't really see the point of knowing the name of the object
MsgBox ActiveSheet.Shapes("Rectangle 1").Name

'Incidentally here is the color of the object
ActiveSheet.Shapes("Rectangle 1").Select
MsgBox Selection.ShapeRange.Fill.ForeColor.SchemeColor
End Sub

I go to my Excel sheet, right-click on rectangle1, Assign Macro -> Rectangle1_WhenClick()
It's done. When I hover over the rectangle, the cursor changes. I click on it and the programmed messages appear.

I hope this helps you a bit. Bye.

Yes, thank you for your help!

The problem is that I have understood the procedure for recording a macro to retrieve all the parameters of my rectangle.

In fact, I have about 30 rectangles, each with a different color and pattern.

I have a macro assigned to all the rectangles (1 to 30).

And I would like this macro to retrieve the parameters of the clicked rectangle to transfer them to another rectangle that I have previously drawn (which is white by default).

This could avoid having to create a macro for each rectangle with a specific color and pattern ... :( let's avoid all that :)
Sub Rectangle1_WhenClicked()
...
End Sub

Sub Rectangle2_WhenClicked()
...
End Sub
...
...
...
Sub Rectangle29_WhenClicked()
...
End Sub
Sub Rectangle30_WhenClicked()
...
End Sub

That way, I only have one macro that retrieves the name of the clicked rectangle, gets the color and pattern of that rectangle, and colors another rectangle using the retrieved parameters ...

It shouldn't be that complicated, should it??? But it's too much for me right now.

Thanks to everyone and especially to Amigo :)

Cool, would someone have the terms to use to retrieve not the name, but the position and dimensions of my rectangle in the same way?