+ Reply to Thread
Results 1 to 7 of 7

Shapes and Deleting Shapes

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    212

    Shapes and Deleting Shapes

    I have a rather complex workbook that was given to me, and the VBA coding is way over my head. The code shown below is called on when a button is clicked on the worksheet to clear all calculations and their related shapes. I have added a couple more rectangle shapes and assigned my own macros to them. When this routine runs, it deletes the shapes I've added along with the ones it is supposed to.

    I need to modify this routine so that the shapes I've added can be passed over the delete action. Had a couple of ideas that failed to work, but it's probably simpler that I think. The Shape Type = is probably one way of doing this but shapes and VBA are all new to me.

    Some assistance would be greatly appreciated.

    Sub ShapesDelete()
        Dim MyShape As Shape, i&
        For Each MyShape In ActiveSheet.Shapes
            i = i + 2
            If i > 14 And MyShape.Type = 1 Then
                MyShape.Delete
            End If
            
    '        If MyShape.name = "Rounded Rectangle 63" Then
    '
    '        End If
            
        Next MyShape
    End Sub

  2. #2
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    212

    Re: Shapes and Deleting Shapes

    I forgot to mention that there are other rectangle shapes on the sheet that aren't deleted when the ShapesDelete sub is run, somehow these have been protected or otherwise excluded.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,659

    Re: Shapes and Deleting Shapes

    This will exclude some shapes by their name.
    Edit the shape names to suit

    Sub ShapesDelete()
        Dim MyShape As Shape, i&
        For Each MyShape In ActiveSheet.Shapes
            Select Case Shape.Name
                Case "Rounded Rectangle 63", "Rounded Rectangle 64", "Rounded Rectangle 65"
                Case Else
                    i = i + 2
                    If i > 14 And MyShape.Type = 1 Then
                        MyShape.Delete
                    End If
            End Select
        Next MyShape
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    212

    Re: Shapes and Deleting Shapes

    Thanks Alpha, added the MyShape to the select case statement and it works great!

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,659

    Re: Shapes and Deleting Shapes

    Quote Originally Posted by rob_h View Post
    Thanks Alpha, added the MyShape to the select case statement and it works great!
    You're welcome. Good catch on my error.

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Shapes and Deleting Shapes

    Or if you want to exclude the shapes which have macros assigned to them, you may try something like this...

    Sub DeleteShapes()
    Dim ws As Worksheet
    Dim shp As Shape
    Set ws = ActiveSheet
    For Each shp In ws.Shapes
        If shp.Type = 1 And shp.OnAction = "" Then
            shp.Delete
        End If
    Next shp
    End Sub
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  7. #7
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    212

    Re: Shapes and Deleting Shapes

    OK, I'll add that one to my notes as well.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. excel 2013 customize context shapes and shapes group menu right click
    By Giuseppe77 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-03-2016, 11:50 AM
  2. excel 2013 customize context shapes and shapes group menu right click
    By Giuseppe77 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-03-2016, 11:50 AM
  3. [SOLVED] followup top shapes as one colour, exclude 2 shapes
    By deancorleone in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-01-2013, 02:14 PM
  4. Deleting Shapes
    By Dean81 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-25-2011, 01:25 PM
  5. How To Move Shapes From Another Shapes? (floating shapes)
    By MarcLev in forum Word Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2009, 04:04 PM
  6. Deleting Specific Shapes
    By FrankStallone in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-15-2006, 01:43 AM
  7. Deleting Shapes
    By aftamath in forum Excel General
    Replies: 5
    Last Post: 11-04-2005, 08:45 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1