VBA Coding problem -text boxes

[Bourbon]

Does anybody know how to rewrite this following code so that it say
"Create a text box 2 spaces to the right of cell C when ever you fin
data in columm C (which it already does) BUT only create a new text bo
two space to the right of columm C if there is not one there alread
(when I rerun the program)...Thus only when new data is entered i
columm C.....

Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")

.TextBoxes.Delete 'delete all existing textboxes???

Set myRng = .Range("c1", .Cells(.Rows.Count, "C").End(xlUp))
For Each myCell In myRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
With myCell.Offset(0, 2)
.Parent.Shapes.AddTextbox _
Orientation:=msoTextOrientationHorizontal, _
Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height
End With
End If
Next myCell
End With
End Sub


This is because I add new data to the sheet often and when I rerun th
code, it erases all preexisting text boxes and creates new own and thu
unfortunetly changing the text box numbers are messing up the rest o
my program....

Thank you very much
B

 

[Guest]

Hi B

Just remove the line of code '.TextBoxes.Delete'

regards
KM

 

[Bourbon]

That is correct KM, but what it does is simply creates another text bo
on top of the old ones and thus hides the old ones which have text i
them. That is why I was looking to simply create a text box if there i
not one there already...any ideas ??

Regards,
B