Put range into textBox

[GeeVeeGee]

Cant get it t work (Excel 2002)
can somone help ?
GeeVeeGee

Sub Range_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. ("Text 1")
Set TxtBox1 = Textbox1

' 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("area1")

'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.
TextBox1.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

[Russell Hauf]

Try something like the following. I'm assuming that this is a text box created with the Control Toolbox.

Sub Range_To_TextBox() 

' Dimension the variables.

Dim strText As String 



    ' Create a For-Each construct to loop through the cells in the range

    For Each cell In ActiveSheet.Range("area1") 

        ' Note: vbCrLf can be used to add a new line in the textbox for

        ' each cell.

        strText = strText & cell.Text & vbCrLf 

    Next cell 

    

    ' Make sure we set the MultiLine property of the textbox to true,

    ' then set its text to the string we just created.

    ActiveSheet.OLEObjects("TextBox1").Object.MultiLine = True 

    ActiveSheet.OLEObjects("TextBox1").Object.Text = strText 

    

End Sub 

Hope it helps,

Russell

[GeeVeeGee]

Will give it a try
thanks Russell

GeeVeeGee

[GeeVeeGee]

Works like a charm

Thanks again Russel

GeeVeeGee

[itr674]

Russell Hauf - trying to use your code to fill / populate a TextBox from the Control TB.

For now I just put the code on the sheet that has TextBox1 on it and I named a range "area1" which is A1:D11. I have also set MultiLine to True.

I had to add Dim celll as Variant.

Now is it possible to list the range in column format or should I just use a ListBox where I could have headers and columns?

Sub Range_To_TextBox()
' Dimension the variables.
Dim strText As String
Dim cell as Variant

    ' Create a For-Each construct to loop through the cells in the range
    For Each cell In ActiveSheet.Range("area1")
        ' Note: vbCrLf can be used to add a new line in the textbox for
        ' each cell.
        strText = strText & cell.Text & vbCrLf
    Next cell
    ' Make sure we set the MultiLine property of the textbox to true,
    ' then set its text to the string we just created.
    ActiveSheet.OLEObjects("TextBox1").Object.MultiLine = True
    ActiveSheet.OLEObjects("TextBox1").Object.Text = strText
End Sub