Hello All,
I hope someone has a solution for me on this problem related to a Text Box and code. I have searched a lot here and found several things regarding code and text and text boxes, but nothing yet that can do what I want, and maybe it’s not possible? I should say that the text box maybe wouldn’t have been my first choice, but it is what I have to work with.
I want to paste/copy text to a textbox from a cell. The text is well over the 255 characters. I found the link to MS and code to loop through to post more that 255 characters. The text I have in the cell has returns in it (for formatting) if that matters. But the code below doesn’t seem to want to work:
Sub Cell_Text_To_TextBox()
' Dimension the variables.
Dim txtBox1 As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
' 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 = ActiveSheet.DrawingObjects(1)
' 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("A1:A10")
'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
I have played with it, and it seems to work if I delete most of the text from the cell, but of course that defeats the whole looping thing. I tried to put my text in several cells (a range like the code shows), but that didn’t seem to work either (doesn’t seem to go to the next cell).
I have several questions for the experts regarding a solution.
First, without using a looping, code – I can click in the cell where all my text is and all of the text show in the formula bar (I know this will display a lot of characters). Then I can highlight all of the text (including the returns) and go to the text box and paste it in. This works great. Can’t I somehow do that with code?? It seems like if I could do that manually, I should be able to do it with code?
I have tried to record a macro of this and what it records is the actual text that is copied, so if the text changes (which it does, based on different circumstances, and this is the whole point), the code doesn’t capture the change; it just pastes the old recorded text. Is there a way to do this process with code? If so, then I could avoid the whole looping thing.
If that is not possible, then does anyone know why this code won’t loop through the text in my cell? Or is there better code out there to do what I want. Another question and point I would like to know, is I can change the starting position to start pasting the characters (startPos =xx). This will not affect the text/characters above the starting position, but deletes everything below it. Can that be changed or is that just the way it is?
ALSO, if the above isn’t asking too much, there is some text that I would like to bold; this doesn’t appear to be easy either. But if I found a way to loop through and paste my text, can I somehow, identify the text I want to bold? Or can I identify particular character positions to bold – I could have all words to be bolded spaced so they are in the same position each time if necessary (they are like headings or titles, etc).
Hopefully the short answer isn’t just – No, can’t do that with Text Boxes. But any help would be greatly appreciated.
Thanks much.
I hope someone has a solution for me on this problem related to a Text Box and code. I have searched a lot here and found several things regarding code and text and text boxes, but nothing yet that can do what I want, and maybe it’s not possible? I should say that the text box maybe wouldn’t have been my first choice, but it is what I have to work with.
I want to paste/copy text to a textbox from a cell. The text is well over the 255 characters. I found the link to MS and code to loop through to post more that 255 characters. The text I have in the cell has returns in it (for formatting) if that matters. But the code below doesn’t seem to want to work:
Sub Cell_Text_To_TextBox()
' Dimension the variables.
Dim txtBox1 As TextBox
Dim theRange As Range, cell As Range
Dim startPos As Integer
' 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 = ActiveSheet.DrawingObjects(1)
' 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("A1:A10")
'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
I have played with it, and it seems to work if I delete most of the text from the cell, but of course that defeats the whole looping thing. I tried to put my text in several cells (a range like the code shows), but that didn’t seem to work either (doesn’t seem to go to the next cell).
I have several questions for the experts regarding a solution.
First, without using a looping, code – I can click in the cell where all my text is and all of the text show in the formula bar (I know this will display a lot of characters). Then I can highlight all of the text (including the returns) and go to the text box and paste it in. This works great. Can’t I somehow do that with code?? It seems like if I could do that manually, I should be able to do it with code?
I have tried to record a macro of this and what it records is the actual text that is copied, so if the text changes (which it does, based on different circumstances, and this is the whole point), the code doesn’t capture the change; it just pastes the old recorded text. Is there a way to do this process with code? If so, then I could avoid the whole looping thing.
If that is not possible, then does anyone know why this code won’t loop through the text in my cell? Or is there better code out there to do what I want. Another question and point I would like to know, is I can change the starting position to start pasting the characters (startPos =xx). This will not affect the text/characters above the starting position, but deletes everything below it. Can that be changed or is that just the way it is?
ALSO, if the above isn’t asking too much, there is some text that I would like to bold; this doesn’t appear to be easy either. But if I found a way to loop through and paste my text, can I somehow, identify the text I want to bold? Or can I identify particular character positions to bold – I could have all words to be bolded spaced so they are in the same position each time if necessary (they are like headings or titles, etc).
Hopefully the short answer isn’t just – No, can’t do that with Text Boxes. But any help would be greatly appreciated.
Thanks much.
