Naming and Deleting Shape

[riteoh01]

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.

[Kenneth Hobs]

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

[riteoh01]

Thanks - but I'm getting a run-time error '70' - Permission denied at line :

s.Name= "ButtonHide"

Any idea why?

[frank_m]

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.

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

[jolivanes]

Or you could check here.

http://www.excelforum.com/excel-programming/818571-working-with-shapes-naming-and-deleting.html

[GTO]

Or you could check here.

http://www.excelforum.com/excel-programming/818571-working-with-shapes-naming-and-deleting.html

Gosh that looks familiar...:shifty:

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

[Kenneth Hobs]

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.