Excel VBA code to copy value in listbox column 1 only of selected row

[buroh]

Hello all,

I have a userform with Listbox1 which shows values from column range (DX20:DY170).

The listbox has two columns.

Column 1 is document number (see below)
Column 2 is description (see below)

So what I am looking to do is to select one or more rows in the list box and click on commandbutton1 which will run vba code to copy the values in column 1 only to clipboard.

Then I can manually paste the copied data into another worksheet or word document.

I got code from another post but as it isn't related to my userform listbox, It didn't work as expected as when I clicked on command button with the code and then pasted the copied data into a word document it pasted the values as ￿￿, so not actual text.

Could anyone please help me with this?

let me know what you need to help me with this code.

Userform1
Listbox1

thank you.

 

[RockEd]

Someone might come along and help out but in the meantime - can you share what code you have so far then we/I can try and see where you are going wrong.

Thanks

 

[buroh]

Someone might come along and help out but in the meantime - can you share what code you have so far then we/I can try and see where you are going wrong.

Thanks

below is what I copied from another thread, so I did originally look for threads to help me, but unfortunately I tried to tweak it for my use but I was unsuccessful.

Dim MyData As DataObject, i As Long, strRow As String
Const strSeparator As String = ", "
With Me.ListBox1
For i = 0 To .ColumnCount - 1
strRow = strRow & .List(.ListIndex, i) & strSeparator
Next i
strRow = Left(strRow, Len(strRow) - Len(strSeparator))
End With
Set MyData = New DataObject
MyData.SetText strRow
MyData.PutInClipboard

Thank you.

 

[RockEd]

Your code works for me....

I put it within the userform code and filled my listbox with a couple of items:

Option Explicit

Private Sub CommandButton1_Click()
Dim MyData As DataObject, i As Long, strRow As String
Const strSeparator As String = ", "
With Me.ListBox1
For i = 0 To .ColumnCount - 1
strRow = strRow & .List(.ListIndex, i) & strSeparator
Next i
strRow = Left(strRow, Len(strRow) - Len(strSeparator))
End With
Set MyData = New DataObject
MyData.SetText strRow
MyData.PutInClipboard
End Sub

Private Sub UserForm_Activate()

With ListBox1
.AddItem "Yes"
.AddItem "no"

End With

End Sub

How did you fill your listbox?

 

[buroh]

Your code works for me....

I put it within the userform code and filled my listbox with a couple of items:

Option Explicit

Private Sub CommandButton1_Click()
Dim MyData As DataObject, i As Long, strRow As String
Const strSeparator As String = ", "
With Me.ListBox1
For i = 0 To .ColumnCount - 1
strRow = strRow & .List(.ListIndex, i) & strSeparator
Next i
strRow = Left(strRow, Len(strRow) - Len(strSeparator))
End With
Set MyData = New DataObject
MyData.SetText strRow
MyData.PutInClipboard
End Sub

Private Sub UserForm_Activate()

With ListBox1
.AddItem "Yes"
.AddItem "no"

End With

End Sub

How did you fill your listbox?

Hello,

This is how my listbox is populated:

Private Sub UserForm_Initialize()

Dim rngMultiColumn As Range

'Define source range
Set rngMultiColumn = Worksheets("Specs & Reports").Range("DX20:DY170")

With ListBox1
'Populate listbox
.List = rngMultiColumn.Cells.Value
End With

End Sub

so the two columns in listbox1 is being populated from the spreadsheet's columns (range).

Thanks.

 

[RockEd]

Something like this should work:

You'll need to change things around if your listbox/commandbutton isn't "1" but that should be fairly straightforward...

Private Sub CommandButton1_Click()
Dim lngItem As Long

  Dim objData As New MSForms.DataObject
    Dim strText

For lngItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lngItem) Then
With Worksheets("Specs & Reports")
strText = ListBox1.List(lngItem, 0)

End With
End If
Next lngItem
    objData.SetText strText
    objData.PutInClipboard
    
End Sub


Private Sub UserForm_Initialize()

Dim rngMultiColumn As Range

'Define source range
Set rngMultiColumn = Worksheets("Specs & Reports").Range("DX20:DY170")

With ListBox1
'Populate listbox
.List = rngMultiColumn.Cells.Value
End With

End Sub

 

[RockEd]

Oh and make sure your "columncount" is the listbox properties = the amount of columns you want it to show (i guess you've already done this though).

 

[buroh]

Oh and make sure your "columncount" is the listbox properties = the amount of columns you want it to show (i guess you've already done this though).

Thanks, but something is not working.

When I paste what is copied into a word document, I get this:

could it be the way I have filled the list box use the vba code I am using that is causing this problem?

I pasted just text into the cell that is populating the textbox, and I am still getting the same paste issue of those two box symbols rather than text.

I do have columncount set correctly.

Thanks

 

[Fluff]

It sounds as though you have a problem with explorer windows open. Have a look here XL2BB 2 Squares

 

[buroh]

It sounds as though you have a problem with explorer windows open. Have a look here XL2BB 2 Squares

Thank you, it was to do with windows explorer being open, but that is going to cause issues, for others who will be using the spreadsheet as most people will have file explorer open. I am not going to always remember to close file explorer to perform the task.

Apart from the obvious issue, is there a fix?