copy and paste item selected from a listbox

PiBi

New Member
Joined
Mar 25, 2010
Messages
16
Hi all,
i'm trying to set up a code which allow me to copy the selected items in a listbox and to past them in some cells in excel.
the problem is that i don't know the code to "explain" that the selected item has to be copied and pasted somewhere in the open sheet.
I tried this but it does not wotk . is there someone able to help me please?

actually i do not know how to write the code to explain that i need to copy the selected value and subsequently to paste it in a cell in excel.


Private Sub CommandButton1_Click()
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select = ListBox1.Text
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=False
End If
Next
End Sub


thanks a lot, bye
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Forget about copy/paste,try

Code:
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).value= ListBox1.Text
 

PiBi

New Member
Joined
Mar 25, 2010
Messages
16
**** it does not work too...

i have tried this, but I still don't know how to write the code (after the red code) to paste the selected item in the listbox...


Private Sub CommandButton1_Click()
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Range("bh1").Select
ActiveSheet.Range("bh1").End(xlDown).Offset(1, 0).Select
End If
Next
End Sub
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Why are you looping thru all the items in the listbox?
 

PiBi

New Member
Joined
Mar 25, 2010
Messages
16

ADVERTISEMENT

because I can have more than one value selected in the listbox..
I think with more than one single value, the Loop is the best way to operate, isn'it? do you have better ideas?
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
because I can have more than one value selected in the listbox..
I think with more than one single value, the Loop is the best way to operate, isn'it? do you have better ideas?


No, I think a loop is fine. I should have asked 'better', but was wondering if it was multi-select. AFAIK, you cannot use .Text or .Value for a multi-select listbox. Try .List instead.

EX: Userform1 with tow command buttons and a listbox (multi-select=True); all objects using default names...

Rich (BB code):
Option Explicit
    
Private Sub CommandButton1_Click()
Dim i As Long
Dim ary
    
    ReDim ary(0 To 0)
    With Me.ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve ary(1 To UBound(ary) + 1)
                ary(UBound(ary)) = .List(i)
            End If
        Next
    End With
    
    Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Offset(1).Resize(UBound(ary)).Value _
        = Application.Transpose(ary)
End Sub
    
Private Sub CommandButton2_Click()
    Unload Me
End Sub
    
Private Sub UserForm_Initialize()
Dim i As Long
    
    For i = 65 To 85
        Me.ListBox1.AddItem Chr(i)
    Next
End Sub

Does that help?

Mark
 

PiBi

New Member
Joined
Mar 25, 2010
Messages
16

ADVERTISEMENT

Hi Weaver, the code you wrote me works only if i set the option Multiselect on 0- fmMultiSelectSingle but it does not work if I work with listbox having the option Multiselect set on 1-fmMultiSelectMulti.
do you know how to adjust the code for listbox having the option Multiselect set on 1-fmMultiSelectMulti?
thanks a lot!!!
 

PiBi

New Member
Joined
Mar 25, 2010
Messages
16
it works GTO! thanks a lot!! it sounds a bit complicated but I will get into in the next minutes! thanks again!
 

PiBi

New Member
Joined
Mar 25, 2010
Messages
16
Hi GTO, i have another small question for you. assume the same problem but this time having two distinct lisbox (for example the first one is a list of men and the second one a list of women) and one singular botton. is there a way to write a code which do the same things for both listbox? in other words, if I select from the first listbox the name ALfred and in the second listbox the name Judith, can we arrange vrtically the two names in column A? thankssss!!
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Hi PiBi,

Sure you should be able to. Not tested/confirmed, but I see nothing to go wrong (which usually means something goes KABOOM!).

We can just add the selected items from the second listbox to the array in the same fashion. I added comments to hopefully make it easier to follow what is happening.

Rich (BB code):
Private Sub CommandButton1_Click()
Dim i As Long
Dim ary
    
    '// Initially size our array with a zer base, then slip it up one upon the first    //
    '// val being assigned to an element.                                               //
    ReDim ary(0 To 0)
    
    With Me.ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                '// resize our array ea time we find a val to add.                      //
                ReDim Preserve ary(1 To UBound(ary) + 1)
                
                ary(UBound(ary)) = .List(i)
            End If
        Next
    End With
    
    '// Just keep adding to the array from items selected in second listbox             //
    With Me.ListBox2
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve ary(1 To UBound(ary) + 1)
                ary(UBound(ary)) = .List(i)
            End If
        Next
    End With
    
    '// I ran up from the bottom and drop down one.  This way we cannot inadvertantly run//
    '// to the bottom of the sheet and try an offset past the sheet.                    //
    '// The we just resize our destination range to the size of the array, transpose    //
    '// the array, and plunk it in.                                                     //
    Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Offset(1).Resize(UBound(ary)).Value _
        = Application.Transpose(ary)
End Sub

Have a great day,

Mark
 

Watch MrExcel Video

Forum statistics

Threads
1,133,582
Messages
5,659,646
Members
418,518
Latest member
chantel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top