Excel & VB - Retrieve the name of the clicked object
ridertart
Posted messages
14
Status
Membre
-
OBO29 -
OBO29 -
Hello,
I would like to know how to retrieve the name of an object that I clicked on (in Excel) using VB.
For example, I have several rectangles and I want to retrieve the color parameters of the rectangle that I clicked on.
However, when I click on a rectangle, it triggers a macro... does this pose a problem for retrieving the name...?
Thank you in advance.
I would like to know how to retrieve the name of an object that I clicked on (in Excel) using VB.
For example, I have several rectangles and I want to retrieve the color parameters of the rectangle that I clicked on.
However, when I click on a rectangle, it triggers a macro... does this pose a problem for retrieving the name...?
Thank you in advance.
Configuration: Windows XP Internet Explorer 7.0
4 réponses
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.
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 :)
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 :)
Ahkrane