[Excel VBA] How to Set Formatting for Selected Shapes

In VBA, we typically manipulate shapes by specifying their names, like Shapes("Name"). However, there are many cases where you want to apply changes to “the shape I am currently selecting” without fixing the target in advance.

Knowing how to handle “currently selected objects” in VBA allows you to create “custom tools” that apply specific formatting, drastically improving your work efficiency. In this article, I will explain how to use Selection.ShapeRange, which is central to this technique.

The Star: Selection.ShapeRange

VBA has a special object called Selection that represents what the user currently has selected. This Selection object possesses different properties depending on whether a cell, a chart, or a shape is selected.

When a user selects one or more shapes (by clicking while holding the Shift key), those shapes can be manipulated through the Selection.ShapeRange property.

Using Selection.ShapeRange eliminates the need to write specific shape names in your code, allowing you to create highly versatile macros.

Practical Code: Applying a “Highlight Style” to Selected Shapes

Preparation: None. Just place some shapes on your worksheet.

The following code is a macro that instantly applies a “Highlight Style” (yellow background with a thick red border) to make the selected shapes stand out. It also includes error handling to prevent errors if no shape is selected.

Sub ApplyHighlightStyleToSelection()

    ' --- Error Prevention ---
    ' If the selected object type is not "DrawingObjects" (Shapes), stop the process
    If TypeName(Selection) <> "DrawingObjects" Then
        MsgBox "Please select a shape before running this macro.", vbExclamation, "Error"
        Exit Sub
    End If

    ' --- Main Process ---
    ' Use Selection.ShapeRange to manipulate selected shapes (single or multiple)
    With Selection.ShapeRange
    
        ' Set fill to bright yellow
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(255, 255, 0)
        
        ' Set border to thick red
        With .Line
            .Visible = msoTrue
            .Weight = 3
            .ForeColor.RGB = RGB(255, 0, 0)
        End With
        
        ' (If there is text) Center align and bold the text
        If .HasTextFrame Then
            With .TextFrame2.TextRange.Font
                .Bold = msoTrue
            End With
            .TextFrame2.HorizontalAnchor = msoAnchorCenter
            .TextFrame2.VerticalAnchor = msoAnchorMiddle
        End If
        
    End With

End Sub

Explanation of the Code

Error Prevention TypeName(Selection) returns the type name of the selected object as a string. If shapes are selected, it returns "DrawingObjects". If it returns something else, a message is displayed, and the macro ends safely. This kind of error handling is crucial for macros used in various situations.

Selection.ShapeRange This single line specifies “all currently selected shapes” as the target of the process. After that, you can simply manipulate properties like .Fill and .Line as usual to change the formatting of all selected shapes at once.

How to Use: Registering to the Quick Access Toolbar

This type of macro shows its true value when it can be called with a single button press. Let’s register it to the Quick Access Toolbar.

1. Right-click the Quick Access Toolbar at the top left of the Excel window and select Customize Quick Access Toolbar.
2. Select Macros from the “Choose commands from” dropdown.
3. Select ApplyHighlightStyleToSelection from the list and click the Add >> button.
4. Select the added macro in the right list and click the Modify button to choose your favorite icon.

Now you can apply the highlight style to selected shapes with just one click at any time.

Summary

In this article, I explained how to manipulate currently selected shapes using Selection.ShapeRange.

• You can target shapes currently selected by the user (single or multiple) using Selection.ShapeRange.
• Using TypeName(Selection) for error handling makes the macro safer and easier to use.
• Registering the created macro to the Quick Access Toolbar turns it into your own original drawing tool.

By distinguishing between macros that specify specific shape names and macros that target selected shapes (like this one), the range of shape manipulation you can perform with VBA expands significantly.