User Form: Multiline text box to multiple rows

[NewBike]

Hi all -
Is there a way to have a multiline textbox separate the values and put
each value into individual cells in a single column? Then apply the
values in the other textboxes (single line entries) to the other
related cells in each row?

For instance:


Truck# Notified Appointment Order#
ws1234 12/1/06 12/2/06 5am 546879
879213
213546


The above information is what would be obtained from the user form,
with the Order number being the multiline entry. I would like to
populate the underlying spreadsheet as shown below, so that
searchs/filters can be done on individual order numbers.


Truck# Notified Appointment Order#
ws1234 12/1/06 12/2/06 5am 546879
ws1234 12/1/06 12/2/06 5am 879213
ws1234 12/1/06 12/2/06 5am 213546


Any assistance/advice would be much appreciated

 

[Conan Kelly]

NewBike,

My suggestion is use MS Access. I think that it would me more appropriate software to use than Excel.

Just my opinion,

Conan

 

[NewBike]

Unfortunately, Access is not an option at this point.

 

[PY & Associates]

After importing,
activate a macro to check from last row up,
if last cell of col A is blank but last cell of col D is NOT blank, copy
next non blank A to C to last cell of col A,
iteration is under your control

Cheers

 

[NewBike]

What I am having a problem with is changing the entry in the multiline text
box on the user form that needs to populate column D.

The user needs to have the ability to enter multiple order numbers coming in

 

[Andy Pope]

Hi,

Try something like this.
It iterates through the orders outputting repeats of the other 2 textboxes.
Obviously change control names and output cell to suit.

You might also need to add a check in the For Each loop to stop blank
lines from appearing.

Private Sub CommandButton1_Click()

Dim vntOrder As Variant
Dim lngRowOffset As Long

' break out multiple orders
For Each vntOrder In Split(TextBox1.Text, vbCrLf)
With Range("A1")
.Offset(lngRowOffset, 0) = TextBox2.Text
.Offset(lngRowOffset, 1) = TextBox3.Text
.Offset(lngRowOffset, 2) = vntOrder
End With
lngRowOffset = lngRowOffset + 1
Next

End Sub

Cheers
Andy