Paste into a 2-column listbox?

[pdp-11]

How do I paste some lines of text into a multicolumn listbox on a
userform? The text will be pasted to the clipboard from. typically, a
text editor. It would be a variable number of lines of text, say 20+.
Then on clicking or tabbing to the listbox to give it the focus I want
to Ctrl-v to paste the text into column 2.

In addition to pastng the text the listbox event would add a line
number in column 1. So if the text on the clipboard was:
Tom
Dick
Harry
This would appear in the listbox as:
1 Tom
2 Dick
3 Harry

I can't even work out which event to use to do the paste.

Grateful for any code segments or pointers.

....then once it's in the listbox the user can drag and drop from the
listbox to a textbox on the same form but we'll leave this for later.
Thanks

 

[FSt1]

hi,
list boxes aren't populated that way.
paste your data on a sheet. in design mode, right click the list box, click
properties.
set the column count property to the number of columns you want which is 3.
set the row source property to the range of your data on the sheet and add
the number indexes to the list. the row source property would look something
like
A1:C20.
and you wont be able to drag and drop from the list box to the text box. you
will have to write code to transfer from the list box to the text box using
most probably the list box click event.
remember. controls don't work like the sheet. not even close. whole
different ball game.
press Alt+F11 on the keyboard. in the VB editor, click help. type list box.
read up on it . you may find some sample code to help. if not post back here
for more help.

Regards
FSt1

 

[NickHK]

You cannot paste directly into a listbox, but you can paste to a range, then
use that as the Rowsource for the listbox.
Assuming you have a column of numbers (1 - 20 in this example), where the
number 1 is a range called "rngData", then try this.
This column of numbers is a bit of a cheat, but if you know approximately
the max number of rows to expect it will work. Otherwise fill the column of
number in code also.
Also you should add a check that there is something to paste, either with a
dataObject or error trapping.

Private Sub CommandButton1_Click()
Dim FillRange As Range

With Worksheets(3).Range("rngData")
.Offset(0, 1).Resize(20, 1).ClearContents
.Offset(0, 1).PasteSpecial

Set FillRange = Range(.Range("A1"), .Offset(0, 1).End(xlDown))
End With

With Me.ListBox1
.ColumnCount = FillRange.Columns.Count
.RowSource = FillRange.Address
End With

End Sub

As for the Drag-Drop:
http://www.vbaexpress.com/forum/archive/index.php/t-454.html

NickHK

 

[pdp-11]

You cannot paste directly into alistbox, but you can paste to a range, then
use that as the Rowsource for thelistbox.
Assuming you have a column of numbers (1 - 20 in this example), where the
number 1 is a range called "rngData", then try this.
This column of numbers is a bit of a cheat, but if you know approximately
the max number of rows to expect it will work. Otherwise fill the column of
number in code also.
Also you should add a check that there is something to paste, either with a
dataObject or error trapping.

Private Sub CommandButton1_Click()
Dim FillRange As Range

With Worksheets(3).Range("rngData")
.Offset(0, 1).Resize(20, 1).ClearContents
.Offset(0, 1).PasteSpecial

Set FillRange = Range(.Range("A1"), .Offset(0, 1).End(xlDown))
End With

With Me.ListBox1
.ColumnCount = FillRange.Columns.Count
.RowSource = FillRange.Address
End With

End Sub

As for the Drag-Drop:http://www.vbaexpress.com/forum/archive/index.php/t-454.html

NickHK










- Show quoted text -

Thanks all - I think I have the solution. Firstly my listbox is not
bound to any worksheets. It is just an intermediate step on a userform
to creating underlying worksheets. User pastes lines of text into the
listbox (which I now realise is impossible) then drags selected lines
into a textbox, adds other text to the textbox then, after some
validation, the contents of the textbox goes to the worksheet(s). The
string built-up in the textbox by the user is a sort of arithmetic
function or formula. So thanks to both your ideas and thoughts I will
have a "Copy Text" command button on the form, its click event will
retrieve the data from the clipboard (this I can do), process the
text to prepend a line number then use this to populate the listbox.
May not be the most efficient but I should have a working solution.

As for drag and drop from a listbox - this I can do and there's an
example in the Excel help.

Thanks again for your help.