Deleting Specific Shapes

Originally Posted by FrankStallone

Hello,

I have a column of data that gets changed by the user, and the second column over copies a shape based on the choice the user made... if they choose Alpha, then they get the shape in cell A27, if they choose Beta, they get the shape in A28. I can make the shapes appear no problem, but I cannot seem to delete the shape that is already in place. So if they change Alpha to Beta, the A28 shape appears ON TOP of the A27 shape.

Here is the code I have so far. Any help would be appreciated.

(The bolded line gives me a Application or UserDefined Error)
------

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RowTarg As Integer
Dim TargVal As String
Dim Choice1 As String
Dim Choice2 As String
Dim Choice3 As String
Dim Choice4 As String
Dim Choice5 As String
Dim Choice6 As String

If Not Intersect(Target, Range("J3:J47")) Is Nothing Then

With ActiveSheet

Choice1 = Cells(1, 27).Value
Choice2 = Cells(2, 27).Value
Choice3 = Cells(3, 27).Value
Choice4 = Cells(4, 27).Value
Choice5 = Cells(5, 27).Value
Choice6 = Cells(6, 27).Value
RowTarg = Target.Row
TargVal = Target.Value

For Each s In .Shapes
If s.TopLeftCell = Target.Offset(0, 2).Address Then s.Delete
Next s


Select Case TargVal
Case Choice1
Cells(1, 26).Copy Destination:=Cells(RowTarg, 15)
Case Choice2
Cells(2, 26).Copy Destination:=Cells(RowTarg, 15)
Case Choice3
Cells(3, 26).Copy Destination:=Cells(RowTarg, 15)
Case Choice4
Cells(4, 26).Copy Destination:=Cells(RowTarg, 15)
Case Choice5
Cells(5, 26).Copy Destination:=Cells(RowTarg, 15)
Case Choice6
Cells(6, 26).Copy Destination:=Cells(RowTarg, 15)
End Select

End With
End If

End Sub

Hi,

Never been there (ie just a guess) , but do you mean s.TopLeftCell.Address

---

Originally Posted by Norgbort

If s.TopLeftCell.Address = Target.Offset(0, 2).Address Then s.Delete

I had tried that, but it still doesn't work. It compiles, but I still get:

Error 1004
Application-defined or Object-defined error

on that particular line.

Any other thoughts?

Hi,

the error says you're looking at the wrong object, and yet I can get shapes deleted from C1 with

Please Login or Register  to view this content.

with no error, when a msgbox says equal the shape goes.

Of course, I don't have real data setup

hth
---