Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Form Text Boxes - Copy format of text boxes | Excel Discussion (Misc queries) | |||
Help with my coding problem? | Excel Worksheet Functions | |||
First attempt at VBA coding problem | New Users to Excel | |||
VBA Coding problem -text boxes | Excel Programming | |||
VBA Coding problem -text boxes | Excel Programming |