-
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.
-
[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]
-
Thanks - but I'm getting a run-time error '70' - Permission denied at line :
s.Name= "ButtonHide"
Any idea why?
-
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]
-
-
Originally Posted by
jolivanes
Gosh that looks familiar...
As does... http://www.vbaexpress.com/forum/showthread.php?t=41279
-
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
-
Forum Rules