Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
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) | |||
Excel VBA coding problem | 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 |