Ah, good point Derk.
Originally Posted by Derk
Helmekki, how about if you just make sure to name the textboxes appropriately? Something like "txtA1" for the textbox in cell A1. Then you could use the following function to determine which textbox corresponds to the active cell:
Use it like so:
Function WhichText(rngTarget As Range) As String
Dim sCurrShape As Shape
Dim szType As String, szAddress As String
WhichText = ""
For Each sCurrShape In ActiveSheet.Shapes
szType = Left(sCurrShape.Name, 3)
szAddress = Right(sCurrShape.Name, Len(sCurrShape.Name) - 3)
If (szType = "txt" And szAddress = rngTarget.Address) Then
WhichText = sCurrShape.Name
Think this will work for you?
Dim szTextToDelete As String
szTextToDelete = WhichText(ActiveCell)
If szTextToDelete <> "" Then ActiveSheet.Shapes(szTextToDelete).Delete