How to format each line in a textbox in VBA Excel

Question

Can I loop through a Textbox for each line entered and format it? For example if the user entered:

Text1
Text2

I would like to output it as in a single line.

'Text1','Text2'

Also would be awesome if you can get the last comma to not display.

gaby · Accepted Answer · 2015-03-27 07:56:24Z

3

This is what you really need

Add this code on TextBox1_Change() event

Dim mystr      

mystr = Split(Sheet1.TextBox1.Text, vbCrLf)
Sheet1.Range("A1") = "'" & Join(mystr, "','") & "'"

And you have the result that you want:

enter image description here

answered Mar 27, 2015 at 0:33

gaby

3061 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks! That works wonderfully. I had it outputting in a test box instead of the sheet so I just replaced Sheet.Range("A1") with Textbox2.Text