Interact with a Multi-Select List Box

[ilya2004]

Hello,

I have a listbox in a userform that interacts with a worksheet that has a list of individuals by country.

I have a macro that runs when a person selects one of the countries using the list-box that colors all of the individuals that are in the country red on the worksheet. It looks approximately like this:

For Each MyCell in Countries
If MyCell.Text = Listbox.Text Then
'code to color etc.
Next

What I would like to do is be able to allow users to select multiple countries using the multi-select option in the listbox and thereby color the cells for multiple countries. I am just not sure how to write the above code for multiple countries.

Any thoughts?
-Ilya

 

[Domenic]

Assuming that the ListBox contains only one column, and that "Countries" is a defined range, place the following code in the UserForm code module for the CommandButton...

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

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()

    [color=darkblue]Dim[/color] MyArray() [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] MatchVal [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] MyCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/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]
                Cnt = Cnt + 1
                [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] MyArray(1 [color=darkblue]To[/color] Cnt)
                MyArray(Cnt) = .List(i)
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] MyCell [color=darkblue]In[/color] Range("Countries")
        MatchVal = Application.Match(MyCell, MyArray, 0)
        [color=darkblue]If[/color] [color=darkblue]Not[/color] IsError(MatchVal) [color=darkblue]Then[/color]
            [color=green]'code to color, etc.[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] MyCell

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Change the control names, accordingly.