VBA code for selected item in listbox

[HYKE]

Hi,

Found this code in the net, what it does reflect the selected item in the msgbox, however this codes takes only the 1st column, how can this code be modified to give the 2nd column and 3rd column data in the msgbox..

Thanks again to all of you for the help..

    Dim ColCnt As Integer
    Dim rng As Range
    Dim cw As String
    Dim c As Integer
    
    ColCnt = ActiveSheet.UsedRange.Columns.Count
    Set rng = ActiveSheet.UsedRange
    With ListBox1
        .ColumnCount = ColCnt
        .RowSource = rng.Address
        cw = ""
        For c = 1 To .ColumnCount
            cw = cw & rng.Columns(c).Width & ";"
        Next c
        .ColumnWidths = cw
        .ListIndex = 0
    End With

 

[Norie]

Have you posted the right code?

That code is for populating the listbox, not displaying the selected item from the listbox.

Anyway, here's how you can do what you want.

Dim I As Long
Dim msg As String
 
    If ListBox1.ListIndex <> -1 Then
        For I = 0 To ListBox1.ColumnCount - 1
            msg = msg & ListBox1.Column(I) & vbCrLf
        Next I
    End If

    MsgBox msg

 

[HYKE]

Hi Norie,

ooppps,I posted the wrong code.

this is the right one

Dim Msg As String
Dim i As Integer
    If ListBox1.ListIndex = -1 Then
        Msg = "Nothing"
    Else
        Msg = ""
        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) Then _
              Msg = Msg & ListBox1.List(i) & vbCrLf
        Next i
    End If

 

[HYKE]

Hi Norie,

Thank you for your code. But what about if my selection in listbox is multiple?
Can this be possible?

Thanks!

 

[Norie]

Is your listbox multiselect?

The first part of that code seems to be treating as being single select and the second part treating it as though it was multiselect.

 

[HYKE]

Hi Norie,

In the userform are 3 options for the user(codes below)

Private Sub obExtend_Click()
    ListBox1.MultiSelect = fmMultiSelectExtended
End Sub
Private Sub obMulti_Click()
    ListBox1.MultiSelect = fmMultiSelectMulti
End Sub
Private Sub obSingle_Click()
    ListBox1.MultiSelect = fmMultiSelectSingle
End Sub

but I prefer multi select as the selection gets transferred to the worksheet.
The code below is for transferring.

Dim lngItem As Long
Dim I As Long
Dim msg As String
 
    If ListBox1.ListIndex <> -1 Then
        For I = 0 To ListBox1.ColumnCount - 1
            msg = msg & ListBox1.Column(I) & vbCrLf
        Next I
    End If

    MsgBox msg
    For lngItem = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(lngItem) Then
            With Sheets("PCA") '< qualify sheet here
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = ListBox1.List(lngItem, 1)
                .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Value = ListBox1.List(lngItem, 2)
            End With
        End If
    Next lngItem

 

[Norie]

If it is a multiselect listbox you don't need this:

 If ListBox1.ListIndex = -1 Then

Try this.

Dim I As Long
Dim J As Long
Dim msg As String
Dim arrItems() As String

    ReDim arrItems(0 To ListBox1.ColumnCount - 1)
    For J = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(J) Then

            For I = 0 To ListBox1.ColumnCount - 1
                arrItems(I) = ListBox1.Column(I, J)
            Next I
            msg = msg & Join(arrItems, ",") & vbCrLf & vbCrLf
        End If
    Next J
    MsgBox msg

Here's code to put the data on a worksheet.

Dim I As Long
Dim J As Long
Dim arrItems()
    ReDim arrItems(0 To ListBox1.ColumnCount - 1)
    For J = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(J) Then

            For I = 0 To ListBox1.ColumnCount - 1
                arrItems(I) = ListBox1.Column(I, J)
            Next I
            
            With Sheets("PCA")
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(, ListBox1.ColumnCount).Value = arrItems
            End With
        End If
    Next J

 

[HYKE]

Thanks Norie, your code worked just as I desired....

 

[inylf]

@Norie, may I ask how can I change your code to put the data on a worksheet, but the data will return in a row instead of a column

 

[silentwolf]

Hi Norie,
after many days looking and trying to find a solution to my problem I found this code of yours!
I do have also a listbox and your code is working fantastic!
However the issue I have is how can I add more textbox values or combobox values in the worksheet as well..

For example if I have a listbox with several employees and other data in textbox or combobox I like those also appear in the worksheet.
Maybe I have more luck with your code and help on this?

Would be very much appreciated!

Albert