Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
I have the following code (from Tom Ogilvy) that I'm using to put certain
text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
Exactly what are you trying to accomplish? Do you have a shape for each one
of the cells in the range and want to assign one cell's text to one shape? Or do you want to concatenate text in all the cells in the range and put that into your single shape? If so, how do you want it concatenated... by rows first, by columns first or as you see it? If none of the above, then what? -- Rick (MVP - Excel) "Paige" wrote in message ... I have the following code (from Tom Ogilvy) that I'm using to put certain text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
hmm
i couldn't do it WITH an object try this ActiveSheet.Shapes(1).Select Selection.Characters.Text = Range("B2") Patrick Molloy (ex MVP) "Paige" wrote: I have the following code (from Tom Ogilvy) that I'm using to put certain text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
I'm not sure if the problem you're having is the concatenation of those values
or if the string is too long when you try to plop it into the rectangle. But this may give you an idea: Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myCell In myRng.Cells sText = sText & " " & myCell.Text Next myCell If Len(sText) 0 Then sText = Mid(sText, 2) End If iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Paige wrote: I have the following code (from Tom Ogilvy) that I'm using to put certain text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
Sorry I didn't explain myself well enough here. Am actually trying to insert
a small table into the shape; the table spans 3 columns and 5 rows. "Dave Peterson" wrote: I'm not sure if the problem you're having is the concatenation of those values or if the string is too long when you try to plop it into the rectangle. But this may give you an idea: Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myCell In myRng.Cells sText = sText & " " & myCell.Text Next myCell If Len(sText) 0 Then sText = Mid(sText, 2) End If iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Paige wrote: I have the following code (from Tom Ogilvy) that I'm using to put certain text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
What's a table?
Is it a picture of that range or what? Paige wrote: Sorry I didn't explain myself well enough here. Am actually trying to insert a small table into the shape; the table spans 3 columns and 5 rows. "Dave Peterson" wrote: I'm not sure if the problem you're having is the concatenation of those values or if the string is too long when you try to plop it into the rectangle. But this may give you an idea: Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myCell In myRng.Cells sText = sText & " " & myCell.Text Next myCell If Len(sText) 0 Then sText = Mid(sText, 2) End If iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Paige wrote: I have the following code (from Tom Ogilvy) that I'm using to put certain text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
Maybe...
Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim sLine As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim myRow As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myRow In myRng.Rows sLine = "" For Each myCell In myRow.Cells sLine = sLine & " " & myCell.Text Next myCell sText = sText & Mid(sLine, 2) & vbLf Next myRow iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Dave Peterson wrote: What's a table? Is it a picture of that range or what? Paige wrote: Sorry I didn't explain myself well enough here. Am actually trying to insert a small table into the shape; the table spans 3 columns and 5 rows. "Dave Peterson" wrote: I'm not sure if the problem you're having is the concatenation of those values or if the string is too long when you try to plop it into the rectangle. But this may give you an idea: Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myCell In myRng.Cells sText = sText & " " & myCell.Text Next myCell If Len(sText) 0 Then sText = Mid(sText, 2) End If iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Paige wrote: I have the following code (from Tom Ogilvy) that I'm using to put certain text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
It is just a range of cells, like A1:B4 below:
Col A Col B Row 1 Code Name Row 2 T2 Smith and Jones Row 3 T4 Wilson Row 4 T5 Johnson and Team Tried the code below, but no luck. What is happening is that data is downloaded from a database each time the user uses the form, and this data populates the range above with a code and name. So I need the shape to be dynamic and always reference what is in that range. Am calling the shape each time the user selects a cell in a specific column of another tab, via Worksheet_SelectionChange. "Dave Peterson" wrote: Maybe... Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim sLine As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim myRow As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myRow In myRng.Rows sLine = "" For Each myCell In myRow.Cells sLine = sLine & " " & myCell.Text Next myCell sText = sText & Mid(sLine, 2) & vbLf Next myRow iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Dave Peterson wrote: What's a table? Is it a picture of that range or what? Paige wrote: Sorry I didn't explain myself well enough here. Am actually trying to insert a small table into the shape; the table spans 3 columns and 5 rows. "Dave Peterson" wrote: I'm not sure if the problem you're having is the concatenation of those values or if the string is too long when you try to plop it into the rectangle. But this may give you an idea: Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myCell In myRng.Cells sText = sText & " " & myCell.Text Next myCell If Len(sText) 0 Then sText = Mid(sText, 2) End If iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Paige wrote: I have the following code (from Tom Ogilvy) that I'm using to put certain text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
What was wrong with the code?
How did you change it? Paige wrote: It is just a range of cells, like A1:B4 below: Col A Col B Row 1 Code Name Row 2 T2 Smith and Jones Row 3 T4 Wilson Row 4 T5 Johnson and Team Tried the code below, but no luck. What is happening is that data is downloaded from a database each time the user uses the form, and this data populates the range above with a code and name. So I need the shape to be dynamic and always reference what is in that range. Am calling the shape each time the user selects a cell in a specific column of another tab, via Worksheet_SelectionChange. "Dave Peterson" wrote: Maybe... Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim sLine As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim myRow As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myRow In myRng.Rows sLine = "" For Each myCell In myRow.Cells sLine = sLine & " " & myCell.Text Next myCell sText = sText & Mid(sLine, 2) & vbLf Next myRow iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Dave Peterson wrote: What's a table? Is it a picture of that range or what? Paige wrote: Sorry I didn't explain myself well enough here. Am actually trying to insert a small table into the shape; the table spans 3 columns and 5 rows. "Dave Peterson" wrote: I'm not sure if the problem you're having is the concatenation of those values or if the string is too long when you try to plop it into the rectangle. But this may give you an idea: Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myCell In myRng.Cells sText = sText & " " & myCell.Text Next myCell If Len(sText) 0 Then sText = Mid(sText, 2) End If iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Paige wrote: I have the following code (from Tom Ogilvy) that I'm using to put certain text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
Sorry to be such a pain, Dave; thanks for your patience. I played around
with it some more this morning and got it to pop up, with the code below. So only 2 more questions: How do I get it to automatically adjust in size to fit the contents, since the # of rows (but not the # of columns) may contract/expand - one time it could be 5 rows, another time 8 rows, etc. Also, since this will be part of worksheet_selectionchange, is there anything special needed in order to anchor it to the selected cell (meaning, I just want it to come up right by the selected cell)? Sub testme() Dim shp As Shape Dim sText As String Dim sLine As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim myRow As Range Dim iCtr As Long Dim Box308 As Shape With ActiveSheet Set shp = .shapes("Rectangle 308") Set myRng = Worksheets("Tables for Pop-Ups").Range("O45:Q55") sText = "" For Each myRow In myRng.Rows sLine = "" For Each myCell In myRow.Cells sLine = sLine & " " & myCell.Text Next myCell sText = sText & Mid(sLine, 2) & vbLf Next myRow iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop shp.Visible = True End With End Sub "Dave Peterson" wrote: What was wrong with the code? How did you change it? Paige wrote: It is just a range of cells, like A1:B4 below: Col A Col B Row 1 Code Name Row 2 T2 Smith and Jones Row 3 T4 Wilson Row 4 T5 Johnson and Team Tried the code below, but no luck. What is happening is that data is downloaded from a database each time the user uses the form, and this data populates the range above with a code and name. So I need the shape to be dynamic and always reference what is in that range. Am calling the shape each time the user selects a cell in a specific column of another tab, via Worksheet_SelectionChange. "Dave Peterson" wrote: Maybe... Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim sLine As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim myRow As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myRow In myRng.Rows sLine = "" For Each myCell In myRow.Cells sLine = sLine & " " & myCell.Text Next myCell sText = sText & Mid(sLine, 2) & vbLf Next myRow iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Dave Peterson wrote: What's a table? Is it a picture of that range or what? Paige wrote: Sorry I didn't explain myself well enough here. Am actually trying to insert a small table into the shape; the table spans 3 columns and 5 rows. "Dave Peterson" wrote: I'm not sure if the problem you're having is the concatenation of those values or if the string is too long when you try to plop it into the rectangle. But this may give you an idea: Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myCell In myRng.Cells sText = sText & " " & myCell.Text Next myCell If Len(sText) 0 Then sText = Mid(sText, 2) End If iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Paige wrote: I have the following code (from Tom Ogilvy) that I'm using to put certain text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
You may be able to count the number of rows that you need and do some arithmetic
to determine the rectangle's height. I'm sure it would entail some trial and error. You can position it with something like: With ActiveSheet Set shp = .Shapes("Rectangle 1") With shp .Top = ActiveCell.Top .Left = ActiveCell.Left + ActiveCell.Width End With ============ You may want to reconsider using a rectangle and use a listbox (from either the Forms toolbar or from the Control toolbox toolbar) or even a textbox from the control toolbox toolbar. Paige wrote: Sorry to be such a pain, Dave; thanks for your patience. I played around with it some more this morning and got it to pop up, with the code below. So only 2 more questions: How do I get it to automatically adjust in size to fit the contents, since the # of rows (but not the # of columns) may contract/expand - one time it could be 5 rows, another time 8 rows, etc. Also, since this will be part of worksheet_selectionchange, is there anything special needed in order to anchor it to the selected cell (meaning, I just want it to come up right by the selected cell)? Sub testme() Dim shp As Shape Dim sText As String Dim sLine As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim myRow As Range Dim iCtr As Long Dim Box308 As Shape With ActiveSheet Set shp = .shapes("Rectangle 308") Set myRng = Worksheets("Tables for Pop-Ups").Range("O45:Q55") sText = "" For Each myRow In myRng.Rows sLine = "" For Each myCell In myRow.Cells sLine = sLine & " " & myCell.Text Next myCell sText = sText & Mid(sLine, 2) & vbLf Next myRow iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop shp.Visible = True End With End Sub "Dave Peterson" wrote: What was wrong with the code? How did you change it? Paige wrote: It is just a range of cells, like A1:B4 below: Col A Col B Row 1 Code Name Row 2 T2 Smith and Jones Row 3 T4 Wilson Row 4 T5 Johnson and Team Tried the code below, but no luck. What is happening is that data is downloaded from a database each time the user uses the form, and this data populates the range above with a code and name. So I need the shape to be dynamic and always reference what is in that range. Am calling the shape each time the user selects a cell in a specific column of another tab, via Worksheet_SelectionChange. "Dave Peterson" wrote: Maybe... Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim sLine As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim myRow As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myRow In myRng.Rows sLine = "" For Each myCell In myRow.Cells sLine = sLine & " " & myCell.Text Next myCell sText = sText & Mid(sLine, 2) & vbLf Next myRow iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Dave Peterson wrote: What's a table? Is it a picture of that range or what? Paige wrote: Sorry I didn't explain myself well enough here. Am actually trying to insert a small table into the shape; the table spans 3 columns and 5 rows. "Dave Peterson" wrote: I'm not sure if the problem you're having is the concatenation of those values or if the string is too long when you try to plop it into the rectangle. But this may give you an idea: Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myCell In myRng.Cells sText = sText & " " & myCell.Text Next myCell If Len(sText) 0 Then sText = Mid(sText, 2) End If iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Paige wrote: I have the following code (from Tom Ogilvy) that I'm using to put certain text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Text to Shape
Great - will do! Thanks for all your help (again)....you're a lifesaver!
"Dave Peterson" wrote: You may be able to count the number of rows that you need and do some arithmetic to determine the rectangle's height. I'm sure it would entail some trial and error. You can position it with something like: With ActiveSheet Set shp = .Shapes("Rectangle 1") With shp .Top = ActiveCell.Top .Left = ActiveCell.Left + ActiveCell.Width End With ============ You may want to reconsider using a rectangle and use a listbox (from either the Forms toolbar or from the Control toolbox toolbar) or even a textbox from the control toolbox toolbar. Paige wrote: Sorry to be such a pain, Dave; thanks for your patience. I played around with it some more this morning and got it to pop up, with the code below. So only 2 more questions: How do I get it to automatically adjust in size to fit the contents, since the # of rows (but not the # of columns) may contract/expand - one time it could be 5 rows, another time 8 rows, etc. Also, since this will be part of worksheet_selectionchange, is there anything special needed in order to anchor it to the selected cell (meaning, I just want it to come up right by the selected cell)? Sub testme() Dim shp As Shape Dim sText As String Dim sLine As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim myRow As Range Dim iCtr As Long Dim Box308 As Shape With ActiveSheet Set shp = .shapes("Rectangle 308") Set myRng = Worksheets("Tables for Pop-Ups").Range("O45:Q55") sText = "" For Each myRow In myRng.Rows sLine = "" For Each myCell In myRow.Cells sLine = sLine & " " & myCell.Text Next myCell sText = sText & Mid(sLine, 2) & vbLf Next myRow iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop shp.Visible = True End With End Sub "Dave Peterson" wrote: What was wrong with the code? How did you change it? Paige wrote: It is just a range of cells, like A1:B4 below: Col A Col B Row 1 Code Name Row 2 T2 Smith and Jones Row 3 T4 Wilson Row 4 T5 Johnson and Team Tried the code below, but no luck. What is happening is that data is downloaded from a database each time the user uses the form, and this data populates the range above with a code and name. So I need the shape to be dynamic and always reference what is in that range. Am calling the shape each time the user selects a cell in a specific column of another tab, via Worksheet_SelectionChange. "Dave Peterson" wrote: Maybe... Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim sLine As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim myRow As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myRow In myRng.Rows sLine = "" For Each myCell In myRow.Cells sLine = sLine & " " & myCell.Text Next myCell sText = sText & Mid(sLine, 2) & vbLf Next myRow iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Dave Peterson wrote: What's a table? Is it a picture of that range or what? Paige wrote: Sorry I didn't explain myself well enough here. Am actually trying to insert a small table into the shape; the table spans 3 columns and 5 rows. "Dave Peterson" wrote: I'm not sure if the problem you're having is the concatenation of those values or if the string is too long when you try to plop it into the rectangle. But this may give you an idea: Option Explicit Sub testme() Dim shp As Shape Dim sText As String Dim mySubStr As String Dim myCell As Range Dim myRng As Range Dim iCtr As Long With ActiveSheet Set shp = .Shapes("Rectangle 1") Set myRng = .Range("s10:u45") sText = "" For Each myCell In myRng.Cells sText = sText & " " & myCell.Text Next myCell If Len(sText) 0 Then sText = Mid(sText, 2) End If iCtr = 1 Do While iCtr < Len(sText) mySubStr = Mid(sText, iCtr, 250) shp.TextFrame.Characters(iCtr).Insert String:=mySubStr iCtr = iCtr + 250 Loop End With End Sub Paige wrote: I have the following code (from Tom Ogilvy) that I'm using to put certain text into a shape; however, cannot figure out how to modify this to accommodate a range of cells (like S10:U45) versus just one cell. Tried using a range name also, but that doesn't work. Can someone help me understand how to do this please? Dim shp As Shape Set shp = ActiveSheet.Shapes("Rectangle 1") sText = Range("A249").Text shp.TextFrame.Characters.Text = sText -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2007 Bug: Text Can be Changed in Shape with Linked Text | Excel Programming | |||
Moving a shape with text inside without editing text | Excel Programming | |||
Cutting and pasting large variable text into a text box shape usin | Excel Programming | |||
Change Shape Text | Excel Worksheet Functions | |||
Testing text on a shape | Excel Programming |