Listbox 'Copy All' for spreadsheet - a solution seeking improvement

I have the need to copy all contents from a Listbox and enable that information to be pasted into a Spreadsheet. My goal was to enable bot OS X and Windows capability for use in Numbers and Excel

The following is what I came up with. After testing this works in both Excel 2016 in Windows 10 and Numbers 4.0.5 OS X 10.12.2. The purpose of this is to share and to see if there are other maybe better ways to accomplish this task.

In ConstructContextualMenu of the listbox add [quote] base.Append( New MenuItem( “Select All and Copy” ) )[/quote]
In ContectualMenuAction add [quote] Dim y As Integer
Dim my_str As String

	Select Case hitItem.Text 
	Case "Select All and Copy" 
			'-- Get headers
			For y = 0 to Me.ColumnCount - 1
					If y = 0 Then
							my_str = Me.Heading(y)
							my_str = my_str + Chr(9) + Me.Heading(y) '-- For most spreadsheets Chr(9) which is VT (Vertical Tab) will enable column seperation when pasting
			my_str = my_str + EndOfLine
			For row As Integer = 0 To Me.ListCount - 1 // Rows are zero-based
					For y = 0 to Me.ColumnCount - 1
							If y = 0 Then
									my_str = my_str + Me.Cell(row,y)
									my_str = my_str + Chr(9) + Me.Cell(row,y)
					my_str = my_str + EndOfLine
	'--- Now that we have all data from Listbox prepared for a spreadsheet place it in clipboard
	Dim c as clipboard 
	c=new clipboard 'C should never be nil, anyway... 
	c.text= my_str 'The selection 
	return true[/quote] 

Now you should be able to paste into Numbers or Excel. Headers should be placed in top row and data will follow

Comments, improvements or other ways to accomplish are welcomed

just get all the text from all cells in a big string.
Using chr(9) to separate columns and endofline for rows.
Than put the text on the clipboard.

Can you do that yourself?

@Christian Schmitz - that’s what the solution I shared does andincludes an explanation of VT. I am curious about other solutions to accomplish the same and be cross platform capable

Sorry I forgot to open that source code section.

Using a string array and join function is more efficient than all the + operations.

To get the Header:

my_str = Me.Heading(-1) + EndOfLine

To get the core data from the Listbox:

my_str = Me.Row(-1,-1) + EndOfLine

To get both “together”:

my_str = Me.Heading(-1) + EndOfLine my_str = my_str + Me.Row(-1,-1) + EndOfLine

Use your code to place the data in the Clipboard.

Ask if there is an error (I have troubles to run Xojo / macOS Sierra ß.

Where do you place the code ?

An error occured while I clicked in Post a Reply… sorry.

And simply call the menu “copy all”

[code]Select Case hitItem.Text

Case “Copy All”

dim AllText as String	

AllText = me.Heading(-1) + EndOfLine
AllText = AllText + me.cell(-1,-1) 
dim c as new Clipboard
c.text = AllText

End Select[/code]

I am not sure that will works in a menu. I forgot why, but I suspect that I had to subclass the Listbox to be able to do that.

That is why I asked where the code was put.

Carl ?

I was not able to get copy all to work. If I manually selected the menu item than all was good. Otherwise In code all I could get was the first list box cell

@Christian Thanks. I do forget about reduced efficiency with + operation. That will be beneficial with large listbox contents

??? Whom do you refer to?

My code works perfectly fine. Did you adjust your menu text accordingly?

And what do you mean by “in code”?

P.S. Added c.close to the code. Maybe that makes a difference on Windows (I’m on a Mac). Shouldn’t as the clipboard is destroyed and its data made available at the end anyway but ir can’t hurt.

Put the code in a Pushbutton (and change Me. by the name of the Listbox).

If this works, that means you have yo subclass the Listbox and put the Copy All Menu Handler there.

I will try it in some minutes after a reboot on El Capitan.

I’m back.

In a new project, put a small Listbox, a TextArea and a button,
keep their names as is.

Sub Open()
Dim LoopIdx As Integer
Dim RowCont As String

RowCont = “Zero” + Chr(9) + “One” + Chr(9) + “Two” + Chr(9) + “Three”

Me.ColumnCount = 4

Me.HasHeading = True
Me.Heading(-1) = RowCont

For LoopIdx = 0 to 9
Me.AddRow Split(RowCont,Chr(9)) // “Zero”,“One”,“Two”,“Three”
End Sub

Sub Action()
Dim AllText As String

AllText = Listbox1.Heading(-1) + EndOfLine
AllText = AllText + Listbox1.cell(-1,-1)

dim c as new Clipboard
c.text = AllText
End Sub

Ctrl+V and watch what you get in the TextArea1 Control (normally what you put in the Clipboard.

Next: the Copy All MenuItem.

@Emile: you do not have to subclass.

OK: I added a Copy All (EditCopyAll) MenuItem,
Add a Menu Handler (EditCopyAll)
Paste the code from the PushButton.

And, Yes (Marcus, you beat me on this one too), no need to subclass. That certainly was for Cut.

PS: my example to fill the Listbox is bad: I’d better add the loop index into one cell.

What I would do is to make it an Extension. Add a Module, call it ListBoxExtensions, and add the following method

[code]Method CopyAllToClipboard(extends lb as ListBox)

dim AllText as String	

AllText = lb.Heading(-1) + EndOfLine
AllText = AllText + lb.cell(-1,-1) 
dim c as new Clipboard
c.text = AllText

Now you can simply call the CopyAllToClipboard for any ListBox like this


(and it also nicely autocompletes)


If EditCopy is used, a subclass is needed.

WARNING: the example shared far above (by me) is a real bad example and, please, do not used it in real project.

I do not select the Listbox (the TextArea have the cocus): very nasty user interface coding. Only shared to make a point.

Carl, is it OK for you now ?

Hey !

I just realized…

Select All

is the same as Copy All !

@Emile S: I could not get that to work as a contextual action. Manually with mouse click on Menu Item yes - program in code no. Must be operator error

@Markus W: Agree with Emile - very Nice. Works in both OS X and Windows 10. I have never used ‘Extends’ in any manner and was not aware. Will need to lookup that feature to see where else that can be utilized

This is what I was hoping for - a learning experience

I would like to thank everyone for their input. Cheers