VBA 2007: How to get object name when inserted in order to delete later

[Spielberg]

Hello all, new to the group - looked around, but couldn't find the result.

Still using Excel 2007 on a Windows 8 laptop.

I write a lot of puzzle and game programs. What I'm able to do is import the "pieces" of this puzzle, but what I'd like to do is be able to delete them.

What I don't know is how to find out the name of the object.

So for example, I have 9 different pieces that get randomly loaded numerous times. There could be 30 copies of piece 1, 42 of piece 2, etc. I guess the quantity of object isn't really important to know...

Is there a way in VBA to have excel tell you the number of each inserted object so I can create code to delete them later?

I hope I worded that properly, I'm not the best writer, although I do talk gooder English then my friends. Sorry.

Thanks to anyone who can help!

Mike

[SamT]

Excel does not have "pieces" collection. It does have a Shapes collection, if that helps


MsgBox Shapes(1).Name

Dim Sh as Object
For each Sh in Shapes
MsgBox sh.name
Sh.Name = "ABC"
Next

For i = 1 to Shapes.Count
MsgBox Shapes(i).name
Next

Shapes("ABC").Name = "XYZ"

Sheets(1).Shapes("ABC").Delete

Dim Pieces As Object
Set Pieces = Sheets(1).Shapes
Pieces("ABC").Delete

[Kenneth Hobs]

Welcome to the forum!

Depends on the object's type. This is just an example. You can delete with error checking without iterating all shapes. If a shape like pictures are:

Sub Main()
Dim s As Shape
For Each s In ActiveSheet.Shapes
Debug.Print s.Name
If s.Name = "Picture 2" Then s.Delete
Next s
End Sub

[Spielberg]

I didn't even think of that, maybe because (in my head) it's not literally a "collection" But I'll surely check that out - thanks!

Mike

[Spielberg]

Thank you for that code! So, I put that in. For some odd reason, it didn't do anything, I wonder why. I will say that my laptop is 10 years old, running Window 8 (can't even upgrade to 8.1) and probably the reason why the code didn't work. Seems 1000% straightforward and perfect.

One thing I forgot to mention (but everyone knows,) is that Excel, will continually add to the "Picture XXX" as the programs runs and pictures are inserted, deleted, add, etc.

Is there a way to reset that? (maybe that should be another post...)

By manually clicking and seeing the picture number, i set up and quick and dirty loop. But BOY is it running slow, and I'm not even selecting the object first!

if it wasn't for your post, I'm sure I wouldn't have done this.

Sub TestDelete()


For X = 1 To 1000


P = "Picture " & X


On Error Resume Next


ActiveSheet.Shapes(P).Delete


Calculate


Next X


End Sub

I needed the calculate in there so my system doesn't hang for an hour... But hmm.. maybe that's the delay?

Thanks again

Mike

[Spielberg]

Another great example, I will try that now, thank you!

[Spielberg]

I'm sorry I forgot to mention the object type. It's just a simple WMF file. Thank you.

[Paul_Hossler]

Try this





Option Explicit


Sub TestDelete()
Dim i As Long


'when deleting, best to go backwards
For i = ActiveSheet.Shapes.Count To 1 Step -1
If Left(ActiveSheet.Shapes(i).Name, 7) = "Picture" Then ActiveSheet.Shapes(i).Delete
Next i


End Sub

[Spielberg]

Just wanted to say thank you to everyone who posted, and sorry i forgot to use the CODE tags - my fault...

After some tinkering, this is what I came up with (but never would have without the help of you great folks!)

It only took about 3 seconds on my old laptop! Thanks again everyone! I'll mark this as solved.



Sub DeletePictures()


For X = 1 To 5000


P = "Picture " & X


On Error Resume Next


ActiveSheet.Shapes(P).Delete


Next X


End Sub

[SamT]

Sub deleteallShapes()
Shapes.SelectAll
Selection.Delete
End Sub

Note that if you Insert all pictures on a Worksheet then you can name each one and refer to it by Name when copying to to another sheet or UserForm


Dim Pieces As Object
Set Pieces = Sheets("Sheet1").Shapes

'Worksheet2 'Insert 'Pieces("ABC")

[Spielberg]

Thank you to everyone (and everyone else) who posted after I marked it solved...

Paul: that code was perfect! in ALL the years I have been using Excel and working with images, I have never used .Count before (actually never needed to.)

But I'll surely not forget this! Thank you all again!

Mike

[Paul_Hossler]

The thing to remember when deleting things is to work backwards, otherwise the indices are messed up

[Spielberg]

Thank you for that tip! I will definitely remember that!

[Paul_Hossler]

Part 2 -- :)

The better (aka more robust) way is to NOT rely on the .Name of a shape (e.g. Picture 4) since the user could change it
I usually rename shapes to keep it easier to use (e.g. 'Picture 4' --> 'Logo')





Option Explicit


Sub TestDelete2()
Dim i As Long


With ActiveSheet
'when deleting, best to go backwards
For i = .Shapes.Count To 1 Step -1
If .Shapes(i).Type = msoPicture Then .Shapes(i).Delete
Next i
End With


End Sub

[Spielberg]

Thank you for that continual amazing information! Actually, the puzzles and stuff I create are output to a .PDF, and unless the user modifies that, then there wouldn't be a change.

But you are DEFINITELY a VBA master!

The .BringToFront command just doesn't work in Excel 2007. So sad...

Thank you again sir!

Mike