Copy Selection from ListBox

[bharatvly]

Hello Fellas,

I need help with the below code. It works fine but not exactly how I want it to work.

What it does?
It copies only the very first column data to clipboard of the selection.

What I want it do?
To copy data entire row data to clipboard of the selection.

Code as below -

Private Sub ListBox1_Click()

Dim MyData As New DataObject
Set MyData = New DataObject
MyData.SetText Received_Emails.ListBox1.Value
MyData.PutInClipboard

End Sub

Please help!

Regards,
Bharat.

 

[AlphaFrog]

Try this. Change the column separator to suit.

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

 

[bharatvly]

Hi AlphaFrog - It works like a charm! Thank you so much!

It however pastes' the data in a single cell when I do paste special values. I'm adding 'Text-to-Columns' function in the macro to separate. Is there any other way out?

Kind Regards,
Bharat.

 

[AlphaFrog]

You're welcome.

There are different ways to do it. You may not need the clipboard at all. What are you trying to do?

if you know the destination row when the user clicks the list box, each listbox column value could be written to the destination row directly.

Or you could write listbox column values to an array and paste the array to a worksheet row.

 

[bharatvly]

This is precisely my intention. I want it to be copied in the active excel sheet behind the UserForm. May I please have the sample code to do that?

 

[AlphaFrog]

It would be best if you described exactly what you wanted instead ofme providing random examples.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ListBox1_Click()
    
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]With[/COLOR] Me.ListBox1
        [COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] .ColumnCount - 1
            ActiveSheet.Cells(6, "A").Offset(i).Value = .List(.ListIndex, i)
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[bharatvly]

Thank you so much AlphaFrog! This is exactly what I was looking for. I modified this a bit to copy the data in a single row rather than in a single column. This is just Perfect!

 

[AlphaFrog]

You're welcome. I missed a comma to copy to a single row.

Private Sub ListBox1_Click()
    
    Dim i As Long
    With Me.ListBox1
        For i = 0 To .ColumnCount - 1
            ActiveSheet.Cells(6, "A").Offset([SIZE=4][COLOR=#FF0000],[/COLOR][/SIZE]i).Value = .List(.ListIndex, i)
        Next i
    End With
    
End Sub