How to rename the selected shape in Excel

Question

If I select a shape, such as a chart or text box, how can I rename it in VBA? I have a nice little sub and form in PowerPoint that does this:

Sub ShapeName()
If Not ActiveWindow.Selection Is Nothing Then
    If ActiveWindow.Selection.Type = ppSelectionShapes Then
        NameForm.NameBox.Text = ActiveWindow.Selection.ShapeRange.Name
        NameForm.Show
        If Not NameForm.bCancel Then
            ActiveWindow.Selection.ShapeRange.Name = NameForm.NameBox.Text
        End If
    End If
End If
End Sub

How can I achieve the same in Excel? The ActiveWindow.Selection object is very different. I can't work out how to navigat from the selection to the selected shape. If the shape selected is a chart object, then the source cells are also selected, and I want to ignore those and just rename the shape.

If you select a chartobject in Excel (2007+ anyway), the selection is usually a chart element such as the chart area, not the actual chartobject - unless you Ctrl+clicked it. It's much easier to just type a new name into the Name box though. — Rory
– Rory, Commented Jun 19, 2014 at 11:26
Ctrl+click doesn't seem to do anything substantially different to normal click, and it doesn't seem possible to rename it in the name box, it's still called "Chart 1" after I try to give it a new name. What I really want to do is to establish a correlation between an Excel chart and a PowerPoint graphic, so I can write some VBA to iterate through a workbook and copy all the charts over into the PowerPoint on top of the existing charts. Giving them names in Excel is the tricky part, as I want to expose this function to the user through a button to rename the currently selected shape. — PhilHibbs
– PhilHibbs, Commented Jun 19, 2014 at 12:00
Click a chart then run msgbox typename(selection); now Ctrl+click and run the same code. If you're only actually interested in charts you could use a simple set cht = activechart inside an error handler. I confess I had forgotten you can't rename charts that way any more. — Rory
– Rory, Commented Jun 19, 2014 at 12:10

Gary's Student · Accepted Answer · 2014-06-19 15:13:19Z

7

Here is a small example:

Sub ARoseByAnyOtherName()
    ActiveSheet.Shapes(1).Select
    Selection.Name = "MyRedRose"
End Sub

EDIT#1:

If we know that the Selected object is a Chart then use:

Sub ARoseByAnyOtherName()
    ActiveChart.Parent.Name = "MyRedRose"
End Sub

edited Jun 19, 2014 at 15:13

answered Jun 19, 2014 at 11:35

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PhilHibbs Over a year ago

This selects a shape and renames it. What I want to do is to rename the currently selected shape, that the user has selected, not that the VBA has selected.

Gary's Student Over a year ago

Then just remove ActiveSheet.Shapes(1).Select

PhilHibbs Over a year ago

If the user selects a chart, then the selection is the chart, and not the shape, and charts cannot be renamed. Shapes can. I need to navigate from the selected chart to the shape and rename that, and I can't find the object relationship path from chart to shape.

PhilHibbs Over a year ago

Aha! The Parent property is a ChartObject so I had assumed that it isn't the same as a Shape. Thanks.

Collectives™ on Stack Overflow

How to rename the selected shape in Excel

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related