Extract ListBox Multi Selection [SOLVED]

[Manish Sharma]

Hi Friends,

I have ActiveX ListBox with MultiSelection option enabled.
I just want the selected items to be extracted in column "I" from Row 3.

Thanks & Regards,
Manish

 

[NARAYANK991]

Hi Manish ,

See if this helps :

http://dailydoseofexcel.com/archives/2004/04/27/using-multiselect-listboxes/

Narayan

 

[Manish Sharma]

Hi Manish ,

See if this helps :

http://dailydoseofexcel.com/archives/2004/04/27/using-multiselect-listboxes/

Narayan

Thanks a lot Narayan, for the weblink. But i have already searched the net and i am finding it little bit difficult to do. If you dont mind can you please help me with the sample file attached in my first post.

Sorry, for asking you to code for me.

Regards,
Manish

 

[SirJB7]

Hi, Manish Sharma!

Try this code:

Option Explicit

Sub ExtractSelected()
    ' constants
    Const ksWS = "Sheet1"
    Const kiColumn = 9
    Const kiRow = 3
    ' declarations
    Dim I As Integer, J As Integer
    ' start
    I = kiRow
    ' process
    With Worksheets(ksWS).ListBox1
        For J = 0 To .ListCount - 1
            If .Selected(J) Then
                .Parent.Cells(I, kiColumn).Value = .List(J)
                I = I + 1
            End If
        Next J
    End With
    ' end
    Beep
End Sub

Regards!

 

[Manish Sharma]

Hi, Manish Sharma!

Try this code:

Option Explicit

Sub ExtractSelected()
    ' constants
    Const ksWS = "Sheet1"
    Const kiColumn = 9
    Const kiRow = 3
    ' declarations
    Dim I As Integer, J As Integer
    ' start
    I = kiRow
    ' process
    With Worksheets(ksWS).ListBox1
        For J = 0 To .ListCount - 1
            If .Selected(J) Then
                .Parent.Cells(I, kiColumn).Value = .List(J)
                I = I + 1
            End If
        Next J
    End With
    ' end
    Beep
End Sub

Regards!

Thanks a lot SirJB7,

Meanwhile I have also tried and succeed in creating a code, but my code will extract multiple selections in a row. Below is the code, may be it will help others.

Private Sub CommandButton1_Click1()
Dim I   As Long, txt As String, Flg As Boolean
Range("B10").Select
Range(Selection, Selection.End(xlToRight)).ClearContents

With Me.ListBox1
    For I = 0 To .ListCount - 1
        If .Selected(I) Then
            Flg = True
            txt = txt & "," & .List(I)
        End If
    Next
End With
If Flg Then
    With Sheets("Sheet1")
        .Range("b10").Value = Mid$(txt, 2)
    End With
End If

'This is to get the selected options from ListBox in seperate cells
    Selection.TextToColumns Destination:=Range("B10"), DataType:=xlDelimited, Comma:=True, TrailingMinusNumbers:=True
End Sub

But I am going to use the code which you have suggested.

Regards,
Manish

 

[SirJB7]

Hi, Manish Sharma!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
PS: Just add this code at the end of the start section and before the process one to clear previous data:

    With Worksheets(ksWS)
        Range(.Cells(kiRow, kiColumn), .Cells(.Rows.Count, kiColumn)).ClearContents
    End With