Let's say I have a list of 100 districts that cover different areas across
the US. I have a list of territory managers that manage several districts.
I have a report that contains 10 columns of data with the district being in
column A. I want to create a list box with all the districts in the listbox
and then allow the territory manager to select the district he/she manages
and then have the spreadsheet only display the districts they selected from
the listbox.

Any ideas on how this can be done?

Thanks

Dwaine Horton

Why not just put a filter on the spreadsheet? (Use the menu Data |
Filter | Auto-Filter.)

Hth,
Merjet

Well, I need to filter on more than one district.

Example: I may have the following districts:
New York
Long Island
Albany
Rochester
Charlotte
Greensboro
Raleigh
Columbia
Chicago
Peoria
Milwaukee

Now I want to show data only for Charlotte, Raleigh, and Columbia and
nothing else.

"merjet" wrote:

Why not just put a filter on the spreadsheet? (Use the menu Data |
Filter | Auto-Filter.)

Hth,
Merjet

Hi

Following from your first post, could you not have an additional column
with the Manager's name against each row.
A table of each of the 100 districts with Manager's name alongside each,
named Managers, would then just require
=VLOOKUP(district,Managers,2,0)
Then use merjet's suggestion of Autofilter to select the Manager name.

--
Regards

Roger Govier


"Dwaine Horton" wrote in
message ...
Well, I need to filter on more than one district.

Example: I may have the following districts:
New York
Long Island
Albany
Rochester
Charlotte
Greensboro
Raleigh
Columbia
Chicago
Peoria
Milwaukee

Now I want to show data only for Charlotte, Raleigh, and Columbia and
nothing else.

"merjet" wrote:

Why not just put a filter on the spreadsheet? (Use the menu Data |
Filter | Auto-Filter.)

Hth,
Merjet

Okay, here's VBA code in response to your original question.

Private Sub ListBox1_Change()
Dim c As Range
Dim rng As Range
Dim iCt As Integer
Set rng = Range(ListBox1.RowSource)
With ListBox1
For iCt = 0 To .ListCount - 1
If .Selected(iCt) = False Then
rng(iCt + 1).EntireRow.Hidden = True
Else
rng(iCt + 1).EntireRow.Hidden = False
End If
Next iCt
End With
End Sub

Alternatively, and sort of like Mr. Govier's idea, add another column
on the worksheet with header "Show?" to be filled with 0's and 1's or
no's and yes's and the user can filter on it.

Hth,
Merjet

One other problem, my code is not seeing the listbox on the worksheet. I
keep getting an object is required with the following line:

Set rng = Range(listbox1.RowSource)

"merjet" wrote:

Okay, here's VBA code in response to your original question.

Private Sub ListBox1_Change()
Dim c As Range
Dim rng As Range
Dim iCt As Integer
Set rng = Range(ListBox1.RowSource)
With ListBox1
For iCt = 0 To .ListCount - 1
If .Selected(iCt) = False Then
rng(iCt + 1).EntireRow.Hidden = True
Else
rng(iCt + 1).EntireRow.Hidden = False
End If
Next iCt
End With
End Sub

Alternatively, and sort of like Mr. Govier's idea, add another column
on the worksheet with header "Show?" to be filled with 0's and 1's or
no's and yes's and the user can filter on it.

Hth,
Merjet