CODEWITHSUNDEEP
excelvba
Canthagar
Canthagar

Reputation: 33

retrieve the second line of a text box in a Worksheet

Dears, I’m trying to retrieve the second line of a text box in a Worksheet,

Ex:
Blue
Mika Ralf
23.05

I would like to copy the name and use it in some other parts of my macro The Text Box was created with Insert  Text Box And the name is TextBox 17 Thank you very much for your help,

Upvotes: 0

Views: 857

Answers (2)

user3598756
user3598756

Reputation: 29421

It's up whether your TextBox is an ActiveX control or a Form control:

from its name ("TextBox 17" has a space in it) I'd say it's a Form control

anyhow here is code for both possibilities

Solution for Form control

Function GetStringFromTextBox_Form(textBoxName As String, strngPos As Integer) As String    
    With ActiveSheet.Shapes(textBoxName).TextFrame.Characters
       GetStringFromTextBox_Form = Split(.Text, "")(strngPos - 1)
    End With    
End Function

to be used like follows

Sub main()
    Dim name As String        

    name = GetStringFromTextBox_Form("TextBox 17", 2) '<~~ use this if your TextBox is a Form Control        
    MsgBox name
End Sub

Solution for ActiveX control

Function GetStringFromTextBox_ActiveX(textBoxName As String, strngPos As Integer) As String
    With ActiveSheet.OLEObjects(textBoxName).Object
        GetStringFromTextBox_ActiveX = Split(.Text, vbCrLf)(strngPos - 1)
    End With
End Function

to be used like follows

Sub main()
    Dim name As String

    name = GetStringFromTextBox_ActiveX("TextBox17", 2) '<~~ use this if your TextBox is an Active X Control        
    MsgBox name
End Sub

Upvotes: 1

gizlmo
gizlmo

Reputation: 1922

Use Split to get all lines of a textbox into an array:

Dim lines() As String

'Split lines of the textbox into array and remove line breaks
lines = Split(Replace(Me.txtTest.Value, Chr(13), ""), Chr(10))

'print line 2
Debug.Print lines(1)

Or if you really just need the second line:

Debug.Print Split(Replace(Me.txtTest.Value, Chr(13), ""), Chr(10))(1)

Here you are directly accessing the second element of the Split function (which is like an array)

Replace Me.txtTest with the name of your Textbox.

Upvotes: 1

Related Questions