Using a VBA Macro how do I determine which shape is selected within a group

Question

Using a VBA Macro how do I determine which shape is selected within a group

Anonymous

I have a group of groups of shapes.

I would like to write a macro, that after user selects one of these sub-shapes the macro will determine which sub-shape is selected and change its fill color.

Currently using this code

If Selection.Type = wdSelectionShape Then

*'Get the shape* 

*Set Sh = ActiveDocument.Shapes(Selection.ShapeRange.Name)* 

*'Color red* 

*Sh.Fill.ForeColor.RGB = vbRed*

It will color the whole group, when a user selects an individual shape in a group, it seems to select the "group shape" too

I am currently doing this in Word, put I OK switching to Excell

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

2 answers

Answer 1

Try the following:

Copy the VBA code below and paste into the VBA editor
Change back to the worksheet containing the shapes
Ungroup the shapes (Can't assign macro to individual shapes while grouped)
Select each shape in turn (right click)
Select "Assign macro" from the dialog
Select the macro name (SelectedShape) and OK. (All shapes assigned to the same macro)
Select all of the shapes required for the group and Regroup
Now you can click an individual shape and the code sets the forecolor.

Additional tip to determine the RGB color code

Select an Ungrouped shape (Doesn't work with grouped shapes)
Click the Dropdown against the Fill color icon on the Home ribbon
Can change color at this point or leave color as is.
Select More colors (bottom of color grid dialog)
Select Custom tab (at top of dialog)
R, G, B color codes displayed. (Can also adjust color here on the spectrum)

NOTE: All of the shapes are assigned to the same VBA macro and Application.Caller determines which shape called the code.

Feel free to get back to me for further assistance if anything not clear or not working as required. Example code tested in Excel Office 365

Sub SelectedShape() 

    Dim strShapeName As String 

    Dim shpSelected As Shape 

    strShapeName = Application.Caller 

    Set shpSelected = ActiveSheet.Shapes(strShapeName) 

    'Following examples of code to set color of shape 

    'Only uncomment one of the lines and comment all others 

    'shpSelected.Fill.ForeColor.RGB = RGB(0, 0, 255) 'Blue 

    'shpSelected.Fill.ForeColor.RGB = RGB(255, 255, 255)   'Green 

    shpSelected.Fill.ForeColor.RGB = vbBlue 'Blue   'Examples of code to set color of shape 

    'shpSelected.Fill.ForeColor.RGB = vbGreen   'Green  

End Sub

Answer 2

Why is there a need for a macro? When I select a shape inside a group I can change the color using the UI:

And if you record a macro during the color change you get a macro like this:

Sub Macro1()
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent4
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.400000006
.Transparency = 0
.Solid
End With
End Sub

Many things in Excel works a bit different as in Word.

And to answer your question about the names, when a shape inside a group is selected you can get the names this way:

Sub Macro2()
MsgBox _
"Shape : " & Selection.Name & vbCrLf & _
"Parent: " & Selection.ShapeRange.ParentGroup.Name
End Sub

Andreas.

Share via

Using a VBA Macro how do I determine which shape is selected within a group

2 answers