Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display only rows with data selected from a listbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display only rows with data selected from a listbox
Why not just put a filter on the spreadsheet? (Use the menu Data |
Filter | Auto-Filter.) Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display only rows with data selected from a listbox
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display only rows with data selected from a listbox
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display only rows with data selected from a listbox
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display only rows with data selected from a listbox
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display only rows with data selected from a listbox
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display only rows with data selected from a listbox
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display only rows with data selected from a listbox
on a worksheet from the Forms toolbar.
"merjet" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display only rows with data selected from a listbox
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display only rows with data selected from a listbox
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. Dwaine Horton wrote: on a worksheet from the Forms toolbar. "merjet" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox data display problem | Excel Programming | |||
Why does AutoFilter display rows NOT selected from drop down box? | Excel Discussion (Misc queries) | |||
display and change listbox data | Excel Programming | |||
Display selected rows from one worksheet to another | Excel Worksheet Functions | |||
Display selected rows and columns in a pop-up window. | Excel Programming |