Multiselect listbox cell link

[Nuz]

I have a listbox on the worksheet where multiple items can be selected. Listbox is created from Forms toolbox (i.e. not from Control Toolbox).

Ho can I determine what are the selected items on the list? I have set a Cell link but it always displays 0. If selection type is 'Single' then it works but I have to use the selection type 'Multiple'.

 

[Domenic]

The following macro lists the index number for the selected items in the first available row in Column A....

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] LB [color=darkblue]As[/color] ListBox
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] LB = ActiveSheet.ListBoxes("List Box 1")
    
    [color=darkblue]With[/color] LB
        [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] .ListCount
            [color=darkblue]If[/color] .Selected(i) [color=darkblue]Then[/color]
                Cells(Rows.Count, "A").End(xlUp)(2).Value = i
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

To list the actual values instead of the index number for the selected items, try replacing...

[font=Verdana]Cells(Rows.Count, "A").End(xlUp)(2).Value = i
            [/font]

with

[font=Verdana]Cells(Rows.Count, "A").End(xlUp)(2).Value = .List(i)
            [/font]

 

[Nuz]

I don't want to use macros in this case. Isn't there a way to make the listbox's cell link to a range of cells rather that a single cell? Or alternatively, if the cell link is set to a single cell, the linked cell value should show an array of selected items, such as {0,2,3,6,7}.

 

[Andrew Poulsom]

I don't want to use macros in this case. Isn't there a way to make the listbox's cell link to a range of cells rather that a single cell? Or alternatively, if the cell link is set to a single cell, the linked cell value should show an array of selected items, such as {0,2,3,6,7}.

Sorry, no.

 

[tas75]

This is what i was looking for, it works perfect!

However, how does the macro have to be adjusted in order to show in column A only the values that are selected each time and on a cumulative way? With this macro, it just adds the new selections below the previous ones, even if the previous are not longer selected - if i unselect one value, it does not disappear from column A.

 

[tas75]

sorry for the mistyping, i meant "and not in a cumulative way".

what i need, is to have on a column the multiple values that are each time selected over a listbox (not in a cumulative way)...

i understand that this is an old thread so if any other than the original members have any ideas, they are more than welcome!

 

[CSCP_EXCEL]

Thank you Domenic for helping me solve this. Tas75, I had the same question , below was my solution..

Sub Testy()

Range("G5:G10").Select
    Selection.Clear
Dim LB As ListBox
Dim i As Long

Set LB = ActiveSheet.ListBoxes("List Box 1")

With LB
For i = 1 To .ListCount
If .Selected(i) Then
Cells(Rows.Count, "G").End(xlUp)(2).Value = i
End If
Next i
End With

End Sub

Regards,

Tyler

 

[CSCP_EXCEL]

I have a follow up question though. I don't want to replace the numbers completely, as the code stands.

I'd like for each list box selection to have a designated cell; so when I concatenate the selection I am given a unique identifier that I can use elsewhere.

So,
G5=1 (Item selected)
G6=0 (No item selected)
G7=3 (3rd Item selected)
G8=0 (No Item Selected)
G9=5 (5th Item Selected)
G10=6 (6th Item Selected)

Then I can have them placed in columns independent, concatenate and the number here is "103056." This dramatically simplifies my selection functions in other dynamic functions of my spreadsheet.

Can anyone lend me a hand or two?

Thanks.

Regards,

Tyler

 

[CSCP_EXCEL]

Thank you Domenic for helping me solve this. Tas75, I had the same question , below was my solution..

Sub Testy()

Range("G5:G10").Select
    Selection.Clear
Dim LB As ListBox
Dim i As Long

Set LB = ActiveSheet.ListBoxes("List Box 1")

With LB
For i = 1 To .ListCount
If .Selected(i) Then
Cells(Rows.Count, "G").End(xlUp)(2).Value = i
End If
Next i
End With

End Sub

Regards,

Tyler

Sorry, not sure how to edit a post. I forgot to mention in my VBA code a couple posts up that I have a header in G4, this is important in regard to where the macro places the numbers. How do I edit?

 

[mikerickson]

If you assign this macro to control, it will put True, False, True (or whatever) below the linked cell.

Sub ListBoxListing()
    Dim LinkedCell As Range
    Dim i As Long

    With ActiveSheet.Shapes(Application.Caller)
        Set LinkedCell = Range(.ControlFormat.LinkedCell)
        With .OLEFormat.Object
            For i = 1 To .ListCount
                LinkedCell.Offset(i, 0) = .Selected(i)
            Next i
        End With
    End With
End Sub