VBA Coding problem -text boxes
 

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

--
Message posted from http://www.ExcelForum.com

VBA Coding problem -text boxes
 

Hi B

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

regards
KM


-----Original Message-----
Does anybody know how to rewrite this following code so
that it says
"Create a text box 2 spaces to the right of cell C when
ever you find
data in columm C (which it already does) BUT only create
a new text box
two space to the right of columm C if there is not one
there already
(when I rerun the program)...Thus only when new data is
entered in
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 the
code, it erases all preexisting text boxes and creates
new own and thus
unfortunetly changing the text box numbers are messing up
the rest of
my program....

Thank you very much
B.


---
Message posted from http://www.ExcelForum.com/

.

VBA Coding problem -text boxes
 

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

--
Message posted from http://www.ExcelForum.com