A
AJL
First off, thanks to everyone for their help so far. I have made great
strides in my project with the aid of suggestions on this forum. One final
task it seems bars me from my ultimate goal.
I have numerous Vlookups in my workbook, that return sizeable paragraphs of
text. The text that is returned depends on values entered into the
worksheet. Now I would like to write a macro that copies and pastes the
returned text, not the formula into a drawn object text box so it can still
be freely edited. I have a macro that does indeed acoomplish this task, but
only so long as the text return is under the 255 character limit for a
string. How can I overcome this limitation for longer and larger bodies of
text? Is it possible to do 255 characters of text in a cell, and then the
next 255 etc. before moving on to the next cell. Here is the macro I have so
far which was designed to do the sort of parsing I described, but as I said
the text only appears when the entire contents of the cell is under the 255
limit.
I appreciate any help you can offer,
Alan
Sub Cell_Text_To_TextBox()
' Dimension the variables.
Dim txtBox1 As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
Dim length As Integer
Set wks1 = Worksheets("Patient1")
' Set txtBox1 equal to the active sheet's TextBox object. You can
' replace the ordinal number with your text box name in quotes.
' For example: ActiveSheet.DrawingObjects("Text 1")
Set txtBox1 = wks1.DrawingObjects("Text Box 36")
' Set a range on the active sheet equal to the range object text
' that you are interested in copying to the text box.
Set theRange = ActiveSheet.Range("D42
72")
'Set the starting position for the text.
startPos = 1
' Create a For-Each construct to loop through the cells in the range.
For Each cell In theRange
' Populate the textbox with the cell values using the Characters
' method.
' Note: Chr(10) can be used to add a new line in the textbox for
' each cell.
txtBox1.Characters(Start:=startPos, _
length:=Len(cell.Value)).Text = cell.Value & Chr(10)
' Update the startPos variable to keep track of where the next
' string of text will begin in the textbox.
startPos = startPos + Len(cell.Value) + 1
Next cell
End Sub
strides in my project with the aid of suggestions on this forum. One final
task it seems bars me from my ultimate goal.
I have numerous Vlookups in my workbook, that return sizeable paragraphs of
text. The text that is returned depends on values entered into the
worksheet. Now I would like to write a macro that copies and pastes the
returned text, not the formula into a drawn object text box so it can still
be freely edited. I have a macro that does indeed acoomplish this task, but
only so long as the text return is under the 255 character limit for a
string. How can I overcome this limitation for longer and larger bodies of
text? Is it possible to do 255 characters of text in a cell, and then the
next 255 etc. before moving on to the next cell. Here is the macro I have so
far which was designed to do the sort of parsing I described, but as I said
the text only appears when the entire contents of the cell is under the 255
limit.
I appreciate any help you can offer,
Alan
Sub Cell_Text_To_TextBox()
' Dimension the variables.
Dim txtBox1 As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
Dim length As Integer
Set wks1 = Worksheets("Patient1")
' Set txtBox1 equal to the active sheet's TextBox object. You can
' replace the ordinal number with your text box name in quotes.
' For example: ActiveSheet.DrawingObjects("Text 1")
Set txtBox1 = wks1.DrawingObjects("Text Box 36")
' Set a range on the active sheet equal to the range object text
' that you are interested in copying to the text box.
Set theRange = ActiveSheet.Range("D42
72")'Set the starting position for the text.
startPos = 1
' Create a For-Each construct to loop through the cells in the range.
For Each cell In theRange
' Populate the textbox with the cell values using the Characters
' method.
' Note: Chr(10) can be used to add a new line in the textbox for
' each cell.
txtBox1.Characters(Start:=startPos, _
length:=Len(cell.Value)).Text = cell.Value & Chr(10)
' Update the startPos variable to keep track of where the next
' string of text will begin in the textbox.
startPos = startPos + Len(cell.Value) + 1
Next cell
End Sub