copy and paste item selected from a listbox

[PiBi]

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

 

[Weaver]

Forget about copy/paste,try

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

 

[PiBi]

**** 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]

Why are you looping thru all the items in the listbox?

 

[PiBi]

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]

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...

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]

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]

it works GTO! thanks a lot!! it sounds a bit complicated but I will get into in the next minutes! thanks again!

 

[PiBi]

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]

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.

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