VBA Coding problem -text boxes (more)
 

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.

VBA Coding problem -text boxes (more)
 

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

--
Regards,
Tom Ogilvy


"Bourbon " wrote in message
...
I was thinking of a new way to fixe my problem of the below code that
says to create a text box if there is data in columm C. First, I have
removed the TextBoxes.Delete line (as KM suggested) but what that did
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 following
code) that would say, run the following code only on new data in columm
C since the last save. That way, my old text boxes would not be
modified and new text boxes would be created if new data was entered in
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 Sub


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

VBA Coding problem -text boxes (more)
 

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

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

VBA Coding problem -text boxes (more)
 

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.

--
Regards,
Tom Ogilvy

"Bourbon " wrote in message
...
Hi Tom, the code you gave me works fine in that it no longer erases the
preexisting text boxes, but when I add new data in columm C (ie, either
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? Any
ideas?

Regards,
B.


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

VBA Coding problem -text boxes (more)
 

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.