Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Location
    Atlanta, Georgia, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Macro to delete a shape or textbox (MS Excel 2000 SR-1)

    Hi,

    I've created one macro and assigned it to a button that creates a text box and a couple of arrows. That works great. I click the button, and everything appears as planned. What I'm having problems with is creating a similar button that removes the text box and arrows. I used the macro recorder, and simply selected the text box and hit delete....same with the arrows. Unfortunately when I try to run the macro (when the text box and arrows are once again on the sheet), I get an error message. When I pulled it up in debug, the following lines are highlighted as being the problem areas:

    ActiveSheet.Shapes("Text Box 12").Select
    Selection.Delete
    ActiveSheet.Shapes("AutoShape 11").Select
    Selection.Delete

    I'm sure it has to do with the generic names being used (i.e. text box 12) but I'm not sure how to overcome this. Is there some way to select all autoshapes on a worksheet, regardless of name, or is there some way that I can provide a unique name to the shape when it is created in the first macro? Any help would be appreciated.

    Thanks,

    Jeff

  2. #2
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Macro to delete a shape or textbox (MS Excel 2000 SR-1)

    Hi, welcome to Woodys Lounge!

    You can use the following macro to remove all shapes from sheet 1:

    Sub delShapes()
    Dim s As Shape
    For Each s In Sheets(1).Shapes
    s.Delete
    Next
    End Sub

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Thanks,

    pmatz

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using Macro to delete a shape or textbox (MS Excel 2000 SR-1)

    In addition to deleting all the shapes (as was provided) you can name a shape with the name property

    <pre> Dim shp As Shape
    Set shp = ActiveSheet.Shapes.AddShape _
    (msoShapeRectangle, 59.25, 76.5, 53.25, 42.75)
    shp.Name = "Bill"</pre>


    You would have to write code to check to ensure the name was unique otherwise naming it would cause a runtime error.

    Then you can delete it with
    <pre> ActiveSheet.Shapes("Bill").Delete</pre>


    Steve

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Using Macro to delete a shape or textbox (MS Excel 2000 SR-1)

    M
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Oct 2003
    Location
    Atlanta, Georgia, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Macro to delete a shape or textbox (MS Excel 2000 SR-1)

    Thanks everyone. I appreciate the help.


    Jeff

Posting Permissions

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