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.

 

[buroh]

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

Thank you, this does work, but only works if windows file explorer is closed, well for me at least. For the code part, this works and is a solution, but something of a windows issue now lol

 

[RockEd]

Perhaps you could write the data to the sheet, and then write another marco in your new sheet/word document to grab this string from your sheet?

 

[Fluff]

Apart from the obvious issue, is there a fix?

I assume there is, as I think @smozgur sorted it with the XL2BB add-in, but I have no idea what the solution is.

 

[smozgur]

I assume there is, as I think @smozgur sorted it with the XL2BB add-in, but I have no idea what the solution is.

The problem is the DataObject of the Microsoft Forms Library object. I don't think it is compatible with the newer Windows operating systems, and in fact, it is a deprecated library as far as I know. The DataObject's PutInClipboard method looks to be conflicting somehow.

Shortly, I decided to NOT use the DataObject in XL2BB when I realized that it causes these unprintable characters in the clipboard, and obviously, it didn't make sense to ask users to avoid explorer windows while using XL2BB - even it was the workaround until we fixed it.

There were a couple of alternatives I considered, so you can try these:

1. Using Windows API for clipboard operations. (I don't like this one at all, but you can find many sample codes online for this)
2. Using late binding to create an HTML document, set its content, and copy it by using the methods that HTML Document Library provides:
   

   CreateObject("htmlfile").parentWindow.clipboardData.setdata "text", strRow

3. Using a hidden text box object (txtCopier maybe) on the userform to set its content and copy it - all native, so I used this one:
   

   With UserForm1.txtCopier
       .Text = strRow
       .SelStart = 0
       .SelLength = .TextLength
       .Copy
   End With

 

[buroh]

The problem is the DataObject of the Microsoft Forms Library object. I don't think it is compatible with the newer Windows operating systems, and in fact, it is a deprecated library as far as I know. The DataObject's PutInClipboard method looks to be conflicting somehow.

Shortly, I decided to NOT use the DataObject in XL2BB when I realized that it causes these unprintable characters in the clipboard, and obviously, it didn't make sense to ask users to avoid explorer windows while using XL2BB - even it was the workaround until we fixed it.

There were a couple of alternatives I considered, so you can try these:

1. Using Windows API for clipboard operations. (I don't like this one at all, but you can find many sample codes online for this)
2. Using late binding to create an HTML document, set its content, and copy it by using the methods that HTML Document Library provides:
   

   CreateObject("htmlfile").parentWindow.clipboardData.setdata "text", strRow

3. Using a hidden text box object (txtCopier maybe) on the userform to set its content and copy it - all native, so I used this one:
   

   With UserForm1.txtCopier
       .Text = strRow
       .SelStart = 0
       .SelLength = .TextLength
       .Copy
   End With

Thank you all for your input and help.

I have managed to solve the windows explorer issue with paste function. I used the 3rd option but tweaked the code slightly.

So for my listbox1 is the code I tweaked:

by using the click function

Private Sub ListBox1_Click()

Dim lngItem As Long

If ListBox1.Selected(lngItem) Then
txtCopier.Text = ListBox1.List(lngItem, 0)

End If
End Sub

Then as suggested with the hidden textbox:

With userform1.txtCopier
.SelStart = 0
.SelLength = .TextLength
.Copy
End With


This combo has managed to put 1st column text into a hidden textbox on the userform and allows a person to copy the value and paste the text in word or excel.

Thank you for all your help peeps.

 

[smozgur]

Glad to hear we were able to help. Thanks for the feedback.