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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,198
Messages
5,509,785
Members
408,752
Latest member
KrisF

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top