Activex Listbox Multiselect

[mrbobdx]

Hey there.

Trying to use a listbox with the multiselect property. Before, when I wasnt using multiselect, I just had the listbox linked to a cell, but that doesnt work with multiselect.

Is there a way to link the listbox to a cell, with the options seperated by commas?

 

[Domenic]

For an ActiveX listbox on a worksheet, right-click the sheet tab, select 'View Code', and copy the following code in the module...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ListBox1_Change()
    [COLOR=darkblue]Dim[/COLOR] sTxt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]With[/COLOR] Me.ListBox1
        [COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] .ListCount - 1
            [COLOR=darkblue]If[/COLOR] .Selected(i) [COLOR=darkblue]Then[/COLOR]
                sTxt = sTxt & ", " & .List(i)
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
        Range(.LinkedCell) = Mid(sTxt, 3)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Change the name of the listbox, accordingly.

Hope this helps!

 

[mrbobdx]

Perfect! Now is there a way that I can delete the contents of the linked cell, and have it unselect all of the listbox items?

 

[Domenic]

Add the following code to your sheet module...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]If[/color] Target.Address(0, 0) = Replace(Me.ListBox1.LinkedCell, "$", "") [color=darkblue]Then[/color]
        [color=darkblue]If[/color] Len(Target) = 0 [color=darkblue]Then[/color]
            [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
            [color=darkblue]With[/color] Me.ListBox1
                [color=darkblue]For[/color] i = 0 [color=darkblue]To[/color] .ListCount - 1
                    .Selected(i) = [color=darkblue]False[/color]
                [color=darkblue]Next[/color] i
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[mrbobdx]

This works if I select only the linked cell, but is there a way to do it where I can select multiple cells at once and it deselects the listbox?

 

[mrbobdx]

Actually, I figured it out. Thanks so much for the help, much appreciated!

 

[Domenic]

I'm not sure what you came up with but maybe something like this...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] rLinkedCell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Set[/COLOR] rLinkedCell = Range(Me.ListBox1.LinkedCell)
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Target, rLinkedCell) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] Len(rLinkedCell) = 0 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
            [COLOR=darkblue]With[/COLOR] Me.ListBox1
                [COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] .ListCount - 1
                    .Selected(i) = [COLOR=darkblue]False[/COLOR]
                [COLOR=darkblue]Next[/COLOR] i
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[mrbobdx]

Even better. Thanks