Deleting a shape and the cell contents the shape is in.

[John DeFiore]

Someone here was kind enough to show me how to delete a
shape that's in the active cell, along with the cell
contents. This is for a macro that's attached to a
button. The user clicks a cell, then that cell and the
shape (basically a dot) in the cell is deleted. No
problem, unless instead of clicking the cell, the user
clicks in the dot in the cell and selects IT instead of
activating the cell. Unhappiness. How would I first
delete any selected shape and the contents of the cell in
which it is (entirely) located? I could then be sure I
had both cases covered and the macro would always work.

Thanks,

John

 

[Dave Peterson]

You could check to see what's selected with:
typename(selection)

Option Explicit
Sub testme()

MsgBox TypeName(Selection)
Select Case TypeName(Selection)
Case Is = "Range"
'cell is selected, do that
Case Else
Selection.TopLeftCell.ClearContents
Selection.Delete
End Select

End Sub

I put some circles/ellipses in some cells and ran the macro. Typename returned
Oval.

You could check for each type that you're using or just quit if it's not a range
(force them to be more careful).

 

[Tom Ogilvy]

if typename(selection) <> "Range" then
selection.TopLeftCell.Select
End if
' your current code

 

[John DeFiore]

Thanks for the reply, Dave.

Regards,

John

-----Original Message-----
You could check to see what's selected with:
typename(selection)

Option Explicit
Sub testme()

MsgBox TypeName(Selection)
Select Case TypeName(Selection)
Case Is = "Range"
'cell is selected, do that
Case Else
Selection.TopLeftCell.ClearContents
Selection.Delete
End Select

End Sub

I put some circles/ellipses in some cells and ran the macro. Typename returned
Oval.

You could check for each type that you're using or just quit if it's not a range
(force them to be more careful).







--

Dave Peterson
(e-mail address removed)
.