Is it possible to extract strings from a Multi-Line TextBox?

[dagda13]

Hi,

I have an Multi-Line ActiveX TextBox1 that has the below strings as separate lines:

'Cat'
'Dog'
'Snake'
'Bear'
'Dragon'

I have TextBox1 linked to a single cell, so that it will output these lines to one cell. I'm trying to figure out a way (if indeed it's even possible) to output each line in TextBox1 so that, for example, you would have

B2 = 'Cat'
C2 = 'Dog'
D2 = 'Snake'
E2 = 'Bear'
F2 = 'Dragon'.

I've tried to add more than one LinkedCells in the TextBox1 Properties, but this doesn't work. Is it possible to somehow extract each string to its own cell? Thanks!

 

[Rick Rothstein]

Assuming the TextBox is on the active worksheet, this macro should do what you want...

Sub TextBoxToRow()
  Dim Arr As Variant
  Arr = Split(ActiveSheet.TextBox1.Text, vbLf)
  Range("B2").Resize(, UBound(Arr) + 1) = Arr
End Sub

 

[dagda13]

Hi Rick,

Thanks very much for the help! However, the input is still outputting all in one cell (B2). I've tried changing the Range both in the Properties and the code, but still no go. (And yes, this textbox is embedded directly in the worksheet).

 

[dagda13]

And sorry, this is totally my bad: What I'd like to do is to output the strings in multiple cells in the same column, ie.,

B2
B3
B4
B5
B6

Sorry again!

 

[Thony]

Please help i tried searching exact word in a textbox multiline

but not working

Dim strVal As String

Static lastInstancePosition As Long ' use Static variable to have its value persist after Sub exiting
Dim instancePosition As Long


strVal = InputBox("Enter receipt number:", "Find")
With TextBox1
instancePosition = InStr(lastInstancePosition + 1, .Text, strVal) 'search string occurrence starting from last found item
If instancePosition > 0 Then
.SetFocus 'bring focus back ti the textbox after it has been taken by the button
.SelStart = instancePosition - 1
.SelLength = Len(strVal)
lastInstancePosition = instancePosition ' update starting position for next search
End If
End With

the Receipt #: 4 is not selected
Thanks in advance

 

[Thony]

Finally i have solve the issue

Private Sub cmdsearch_Click()
Dim a() As String
Dim b As String
Dim strVal As String
Static lastInstancePosition As Long ' use Static variable to have its value persist after Sub exiting
Dim instancePosition As Long


strVal = InputBox("Enter receipt number:", "Find")

With TextBox1
a = Split(.Text, vbCrLf)
instancePosition = InStr(lastInstancePosition + 1, Join(a, vbLf), strVal) 'search string occurrence starting from last found item
If instancePosition > 0 Then
.SetFocus 'bring focus back to the textbox after it has been taken by the button
.SelStart = instancePosition - 1
.SelLength = Len(strVal)
lastInstancePosition = instancePosition ' update starting position for next search
End If
End With
End Sub