Copy data from excel listbox to clipboard

[KUYJS]

Hi,
I have one excel userform that has one listbox. I am adding 2 to 5 lines to it using Listbox1.Additem and once all items are added i want to copy this entire data to clipboard so that i may paste it to web based application uaing ctrl +v.

Kindly help
Regards,
Tarun

 

[smozgur]

Since there is a userform, and MSForms library is already loaded, MSForms.Dataobject can be used to interact with the clipboard.

Following code might give an idea.
Assuming there is a listbox called ListBox1, and items are already loaded.

Sub copyToClipboard()
Dim dataObject As New MSForms.dataObject
Dim tmpList As String
    ' Put the listbox items into a string, delimited with new line
    ' You can use another way for this, storing the data in the 
    'variable while your code is executing AddItem etc..
    With Me.ListBox1
        For i = 0 To .ListCount - 1
            tmpList = tmpList & .List(i) & vbCrLf
        Next i
    End With
    
    ' Use MSForms DataObject to send the variable data to the clipboard
    With dataObject
        .SetText tmpList
        .PutInClipboard
    End With
End Sub