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
' Initialize the return value to a blank string
WhichText = ""
For Each sCurrShape In ActiveSheet.Shapes
' Get the type of shape
szType = Left(sCurrShape.Name, 3)
' Get the address referenced by the shape
szAddress = Right(sCurrShape.Name, Len(sCurrShape.Name) - 3)
' If the shape is a textbox referencing the target address
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