Murray Cooper
asked on
Excel Shapes: How many and how to delete
Have created and excel chart using VBA which I have assigned an id no. to
'ActiveSheet.Shapes.AddCha
Does excel assign any other count mechanism each time this runs? ie Does excel only have the one shape identified as AddChart(2) or may there be others. How do I find out?
Now I want to delete this shape and only this shape when the user has finished with the chart eg Charts 3, 4 ... etc. I also have forms controls. Are these also shapes? (don't want to delete them!!)
I am not a vba expert - just learning. I have jacked my code from all over the net. But it uses different terminologies in places which is confusing me:
ActiveSheet.Shapes.AddChar
ActiveChart.SetSourceData Source:=myRange
ActiveChart.ChartType = xlLine
With ActiveChart.Parent < --- here it has been adopted
.Height = 375 ' resize
.Width = 600 ' resize
.Top = 90 ' reposition
.Left = 730 ' reposition
End With
With ActiveChart.SeriesCollecti
ActiveChart.SetElement (msoElementLineHiLoLine)
Selection.Format.Line.Visi
End With
Any enlightenment will be appreciated.
PS all the code works fine .... for a stitch and patch job
Thank you
'ActiveSheet.Shapes.AddCha
Does excel assign any other count mechanism each time this runs? ie Does excel only have the one shape identified as AddChart(2) or may there be others. How do I find out?
Now I want to delete this shape and only this shape when the user has finished with the chart eg Charts 3, 4 ... etc. I also have forms controls. Are these also shapes? (don't want to delete them!!)
I am not a vba expert - just learning. I have jacked my code from all over the net. But it uses different terminologies in places which is confusing me:
ActiveSheet.Shapes.AddChar
ActiveChart.SetSourceData Source:=myRange
ActiveChart.ChartType = xlLine
With ActiveChart.Parent < --- here it has been adopted
.Height = 375 ' resize
.Width = 600 ' resize
.Top = 90 ' reposition
.Left = 730 ' reposition
End With
With ActiveChart.SeriesCollecti
ActiveChart.SetElement (msoElementLineHiLoLine)
Selection.Format.Line.Visi
End With
Any enlightenment will be appreciated.
PS all the code works fine .... for a stitch and patch job
Thank you
ASKER
Hello Rgonzo1971
Thank you for a very prompt response.
Ok I think I understand your code.
Is there only one Parent?
Also, where does excel/vba store all the unique identifiers it attaches to shapes and objects? How do I find the unique identifier (number)attached to my shape?
Also to try and understand the logic of my work. Please correct/ append where necessary
A Shape is a vba Object. A Chart is just one type of shape and is subordinate to Shape.
ActiveSheet.Shapes.AddChar
Having added it I can directly talk to the Chart object (vba knows it is a Shape) eg Active Chart Parent or Active Chart Series collection
Is this basically correct?
Again, Thank You for your time, help and Patience
Regards
Murray
Lvl Basement
Thank you for a very prompt response.
Ok I think I understand your code.
Is there only one Parent?
Also, where does excel/vba store all the unique identifiers it attaches to shapes and objects? How do I find the unique identifier (number)attached to my shape?
Also to try and understand the logic of my work. Please correct/ append where necessary
A Shape is a vba Object. A Chart is just one type of shape and is subordinate to Shape.
ActiveSheet.Shapes.AddChar
Having added it I can directly talk to the Chart object (vba knows it is a Shape) eg Active Chart Parent or Active Chart Series collection
Is this basically correct?
Again, Thank You for your time, help and Patience
Regards
Murray
Lvl Basement
A chart is not a shape but
ActiveChart.Parent which is an ChartObject is one type of Shape
Chart and Chartobjects are not the same
The name of the ChartObject does not change automatically in contrast to the index number
If you gave your ChartObject a unique name it will be then easy to retrieve
ActiveChart.Parent which is an ChartObject is one type of Shape
Chart and Chartobjects are not the same
The name of the ChartObject does not change automatically in contrast to the index number
If you gave your ChartObject a unique name it will be then easy to retrieve
I'm sorry to disagree but a chart is a kind of shape. Also there is no need to refer to its parent and this will work just fine.
ActiveSheet.Shapes("Chart 1").Delete
Some more info about shapes. If you ever forget their names there are at least 3 ways to find out what they are:
1. Right click on the shape and the name will appear in the 'Name Box" (in the upper left-hand corner above the sheet)
2, Run code like the following
1. Right click on the shape and the name will appear in the 'Name Box" (in the upper left-hand corner above the sheet)
2, Run code like the following
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
Debug.Print shp.Name
Next
@Martin
when you create the chart with the code mentioned
You cannot delete with
the shape name that you get from the for each code is the ChartObject not the Chart
when you create the chart with the code mentioned
You cannot delete with
ActiveSheet.Shapes(Activechart.Name).DeleteActiveSheet.Shapes(Activechart.Parent.Name).Deletethe shape name that you get from the for each code is the ChartObject not the Chart
And finally, credit to expert byundt for this warning.
Code like the following snippet should never be used! While it will delete some objects that are obviously shapes, it will also delete useful shape objects like data validation dropdowns, cell comments, Forms and Control ToolBox command buttons, charts and explanatory textboxes. The data validation dropdowns are deleted irreversibly (you can never add another one to the worksheet). To recover data validation dropdowns, you must insert another worksheet and recreate the content, then delete the "shapeless" original.
Code like the following snippet should never be used! While it will delete some objects that are obviously shapes, it will also delete useful shape objects like data validation dropdowns, cell comments, Forms and Control ToolBox command buttons, charts and explanatory textboxes. The data validation dropdowns are deleted irreversibly (you can never add another one to the worksheet). To recover data validation dropdowns, you must insert another worksheet and recreate the content, then delete the "shapeless" original.
Sub ClearAllPins()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 4) <> "Keep" Then shp.Delete
Next
End Sub
@Rgonzo1971. I ran the AddChart sub and it added Chart 4. When I ran the DeleteIt sub it was deleted. Am I confused about what you are saying?
Sub AddChart()
With ActiveSheet.ChartObjects.Add _
(Left:=200, Width:=375, Top:=75, Height:=225)
.Chart.ChartType = xlXYScatterLines
End With
End Sub
Sub DeleteIt()
ActiveSheet.Shapes("Chart 4").Delete
End Sub
HI,
if you try
The ChartObject object acts as a container for a Chart object
https://msdn.microsoft.com/en-us/library/office/ff838184.aspx
if you try
Sub AddChart()
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.ChartType = xlXYScatterLines
End With
End Sub
Sub DeleteIt()
ActiveSheet.Shapes(ActiveChart.Name).Delete ' wont work
'ActiveSheet.Shapes(ActiveChart.Parent.Name).Delete 'this works
End SubThe ChartObject object acts as a container for a Chart object
https://msdn.microsoft.com/en-us/library/office/ff838184.aspx
OK, I don't want to hijack this thread any more than we have but while what you say in your last post is true, you could go out of your way to avoid using the Parent's name by doing this (but I never would).
ActiveSheet.Shapes(Replace(ActiveChart.Name, ActiveSheet.Name & " ", "")).DeleteASKER
Thank you both, Martin and Rgonzo1971.
Martin your comments re the index number and how to find them were very helpful and what I was seeking. Thank you +1
Rgonzo1971, I am sorry, but I have now read elsewhere in my research that a chart is a Shape.
...Another complication is that embedded sheets are now shapes (Chart Objects are a special kind of shape). So rather than having a Chart Object collection, embedded charts get added to the shapes collection.
http://www.homeandlearn.org/vba_embedded_charts.html
... ' insert and populate chart
Set cht = ws.Shapes.AddChart2(240, xlXYScatterLines).Chart
Peltier Tech Blog
I found out experimenting with delete shapes - chart disappeared - so did my forms controls.
Hence my comment that vba knows it is a shape - that's why it creates it and I do not need to refer to Shape.Chart any further.
I cant create it unless I call it as a shape OR Chart Objects Add
Your hyperlink does not dispel this. It only clarifies about Objects and their container(s)
But again, Thank you both for your interest and your assistance
Regards
Murray
Martin your comments re the index number and how to find them were very helpful and what I was seeking. Thank you +1
Rgonzo1971, I am sorry, but I have now read elsewhere in my research that a chart is a Shape.
...Another complication is that embedded sheets are now shapes (Chart Objects are a special kind of shape). So rather than having a Chart Object collection, embedded charts get added to the shapes collection.
http://www.homeandlearn.org/vba_embedded_charts.html
... ' insert and populate chart
Set cht = ws.Shapes.AddChart2(240, xlXYScatterLines).Chart
Peltier Tech Blog
I found out experimenting with delete shapes - chart disappeared - so did my forms controls.
Hence my comment that vba knows it is a shape - that's why it creates it and I do not need to refer to Shape.Chart any further.
I cant create it unless I call it as a shape OR Chart Objects Add
Your hyperlink does not dispel this. It only clarifies about Objects and their container(s)
But again, Thank you both for your interest and your assistance
Regards
Murray
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the Chart Index help +1
... I still think a chart IS a Shape, as explained
Regards
Murray
Lvl Basement
... I still think a chart IS a Shape, as explained
Regards
Murray
Lvl Basement
You're welcome and I'm glad I was able to help.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
pls try
Open in new window
Regards