Consulting

Results 1 to 7 of 7

Thread: Naming and Deleting Shape

  1. #1
    VBAX Newbie
    Joined
    Oct 2011
    Posts
    5
    Location

    Naming and Deleting Shape

    Hi,

    I'm using the following code to create and name a rectangle shape on my workbook :

    ActiveSheet.Shapes.AddShape(msoShapeRectangle, 736.25, 330#, 97.25, 63#).Select
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 65
    Selection.ShapeRange.Fill.Transparency = 0#
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    With Selection
    .Name = ButtonHide
    End With

    The problem I'm now coming up against, is trying to delete this same shape with another macro.

    I'm no VBA expert - I simply learn off others, and what I can read on the net - so have I named the shape correctly, and if I have, how do I delete this named shape?

    Any help appreciated.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    [VBA]Sub AddButton()
    Dim s As Shape
    Set s = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 736.25, 330#, 97.25, 63#)
    With s.Fill
    .Visible = msoTrue
    .Solid
    .ForeColor.SchemeColor = 65
    .Transparency = 0#
    End With
    With s.Line
    .Weight = 0.75
    .DashStyle = msoLineSolid
    .Style = msoLineSingle
    .Transparency = 0#
    .Visible = msoTrue
    End With
    s.Name = "ButtonHide"
    End Sub

    Sub RemoveButton()
    ActiveSheet.Shapes("ButtonHide").Delete
    End Sub[/VBA]

  3. #3
    VBAX Newbie
    Joined
    Oct 2011
    Posts
    5
    Location
    Thanks - but I'm getting a run-time error '70' - Permission denied at line :

    s.Name= "ButtonHide"

    Any idea why?

  4. #4
    VBAX Expert
    Joined
    Sep 2010
    Posts
    604
    Location
    HI riteoh01,

    Usually that error is because a shape by that name already exist.

    Try calling the delete shape macro at the begining of the code and adding on error resume next as shown below.
    [vba]
    Sub AddButton()
    Dim s As Shape

    Call RemoveButton 'delete rectangle button named "ButtonHide" if it already exists

    Set s = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 736.25, 330#, 97.25, 63#)
    With s.Fill
    .Visible = msoTrue
    .Solid
    .ForeColor.SchemeColor = 65
    .Transparency = 0#
    End With
    With s.Line
    .Weight = 0.75
    .DashStyle = msoLineSolid
    .Style = msoLineSingle
    .Transparency = 0#
    .Visible = msoTrue
    End With

    s.Name = "ButtonHide"
    End Sub

    Sub RemoveButton()
    On Error Resume Next' in case of error trying to delete a shape that does not exist.
    ActiveSheet.Shapes("ButtonHide").Delete
    End Sub

    [/vba]

  5. #5

  6. #6
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Quote Originally Posted by jolivanes
    Gosh that looks familiar...

    As does... http://www.vbaexpress.com/forum/showthread.php?t=41279

  7. #7
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    This was a duplicate post on this forum and duplicated on the other forum. For cross-posting information see: http://www.excelguru.ca/node/7

    I can not produce that error. Running multiple times creates multiple shapes with the same name.

    I do get an error on the first line of code if the sheet is protected.

    Try running it from a new workbook. If needed, post the problem workbook.

Posting Permissions

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