Consulting

Results 1 to 15 of 15

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

  1. #1

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

    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

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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

  4. #4
    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

  5. #5
    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

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

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

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    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

  10. #10
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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")
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  11. #11
    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

  12. #12
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    The thing to remember when deleting things is to work backwards, otherwise the indices are messed up
    Last edited by Paul_Hossler; 05-30-2020 at 07:04 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  13. #13
    Thank you for that tip! I will definitely remember that!

  14. #14
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #15
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •