Display only rows with data selected from a listbox

[Dwaine Horton]

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
(e-mail address removed)

 

[merjet]

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

Hth,
Merjet

 

[Dwaine Horton]

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.

 

[Roger Govier]

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.

 

[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

 

[Dwaine Horton]

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]

W/o seeing what you have, I can only guess.
1. Does your RowSource look like this? Sheet1!A2:A12
2. You may need to qualify it like:
Set rng = Sheets("Sheet1").Range(listbox1.RowSource)

Hth,
Merjet

 

[merjet]

Hold on. What kind of ListBox are you using? 1. On a UserForm. 2. On
a worksheet from the Forms toolbar. 3. On a worksheet from the Control
Toolbox toolbar.

Hth,
Merjet

 

[Dwaine Horton]

Hold on. What kind of ListBox are you using? 1. On a UserForm. 2. On
a worksheet from the Forms toolbar. 3. On a worksheet from the Control
Toolbox toolbar.

Hth,
Merjet

 

[merjet]

Sub ListBox1_Change()
Dim iCt As Long
With Worksheets("Sheet1").ListBoxes("List Box 1")
Set rng = Range("A2:A12") ' Input range
For iCt = 1 To .ListCount
If .Selected(iCt) = False Then
rng(iCt).EntireRow.Hidden = True
Else
rng(iCt).EntireRow.Hidden = False
End If
Next iCt
End With
End Sub

Hth,
Merjet

 

[Dave Peterson]

I created a workbook with two worksheets--sheet1 and sheet2.

Sheet1 held the data with the district names in column A (with A1 holding the
header).

Sheet2 held the list of unique district names in column A (with A1 holding the
same header that was used in Sheet1).

Then I put a listbox from the Forms toolbar on Sheet1 and a button from that
same toolbar right under the listbox.

I rightclicked on the listbox and chose Format Control
On the control tab, I made the input range Sheet2!a2:a21 (I only used 20
entries)

And I chose Multi in the selection type.

Then I assigned this macro to the button:

Option Explicit
Sub testme()
Dim myLB As ListBox
Dim iCtr As Long
Dim DestCell As Range
Dim myCriteria As Range
Dim HowMany As Long

Dim ActWks As Worksheet
Dim ListWks As Worksheet

Set ActWks = Worksheets("sheet1")
Set ListWks = Worksheets("sheet2")

With ListWks
.Range("c:c").Clear
.Range("c1").Value = .Range("A1").Value
Set DestCell = .Range("c2")
End With

With ActWks
Set myLB = .ListBoxes("list box 1")
With myLB
HowMany = 0
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
HowMany = HowMany + 1
DestCell.Value _
= "=" & Chr(34) & "=" & .List(iCtr) & Chr(34)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

If HowMany = 0 Then
MsgBox "Please select at least one item!"
Exit Sub
End If

Set myCriteria = ListWks.Range("c1").Resize(HowMany + 1)
.Range("a:a").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=myCriteria
End With

End Sub

This builds a list in on sheet2 based on the selected items in that listbox. C1
will hold the header. C2:C## will hold the selected items.

Then it essentiall runs data|filter|advanced filter with a criteria range of
what's in column C of sheet2.