VBA Coding problem -text boxes (more)

[Bourbon]

I was thinking of a new way to fixe my problem of the below code tha
says to create a text box if there is data in columm C. First, I hav
removed the TextBoxes.Delete line (as KM suggested) but what that di
is simply create a new text box on top of the old ones. I was thinking
can a code be written (that would be incorporated in the followin
code) that would say, run the following code only on new data in colum
C since the last save. That way, my old text boxes would not b
modified and new text boxes would be created if new data was entered i
columm C..
Thanks again
B.

Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")


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 Su

 

[Tom Ogilvy]

Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")


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)
bSkip = False
for each tbox in Activesheet.Textboxes
if tbox.TopleftCell.Address = .Address then
bSkip = True
exit for
end if
Next
if not bSkip then
Parent.Shapes.AddTextbox _
Orientation:=msoTextOrientationHorizontal, _
Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height
End if
End With
End If
Next myCell
End With
End Sub

 

[Bourbon]

Hi Tom, the code you gave me works fine in that it no longer erases th
preexisting text boxes, but when I add new data in columm C (ie, eithe
a B,S,H or TP) I get the follwoing message:

Run-time error 438
Object does not support or recognize this method....

Area that is highlited is:
Parent.Shapes.AddTextbox _
Orientation:=msoTextOrientationHorizontal, _
Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height

So it no longer adds a text box when new data is found in columm C? An
ideas?

Regards,
B

 

[Tom Ogilvy]

Parent should have a fullstop/period in front of it:

.Parent.Shapes.AddTextbox _
Orientation:=msoTextOrientationHorizontal, _
Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height

I would assume your original code (in Excel) did have the period - it was
not my intention to remove it - I think the mail software is removing the
period if it appears on the left margin.

In your original posting it was not there and I did not add it back in as I
didn't notice it was missing.

 

[Bourbon]

Thanks for noticing that, what an obvious mistake. I apologize! I'm only
a rookie and learning as I go.

Thank you once again
B.