Pasting data from text box to cell with VBA

Joon16

New Member
Joined
Feb 5, 2016
Messages
4
I am using the following code to paste a text box into a cell and it is working fine, but I would like the text broken out into multiple cells rather than all in 1 cell. How can I change the code to do that?

For example, the text box has data like this (all in 1 text box named Text Box 13):

Explanation:
A: input explanation here
B: input explanation here
C: input explanation here

All of this data is currently being copied to cell AE2, however, I would like to have "Explanation" in cell AE2, "A: input explanation here" in cell AE3, "B: input explanation here" in cell AE4, etc.

Dim txBox As Shape
Set txBox = ActiveSheet.Shapes("Text Box 13")

Range("AE2").Value = txBox.TextFrame.Characters.Text

Thank you all in advance!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Should be easy enough if there's only 4 lines, but I imagine that's not the case? If so, only slightly more difficult.
 
Upvote 0
What matters is how the lines are created. If each line ends with a carriage return/line feed then this should work.
VBA Code:
Dim i As Integer
Dim sParts() As String, strIn As String

strIn = TextBox1.Text
sParts = Split(strIn, vbCrLf)
For i = 0 To UBound(sParts, 1)
     Range("AE" & i + 2) = sParts(i)
Next
I don't have Sub End Sub because I don't know how you want to trigger the code.
 
Upvote 0

Forum statistics

Threads
1,215,427
Messages
6,124,830
Members
449,190
Latest member
rscraig11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top