Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box and Multiple row / column display
Hi
Been racking my brains for a few days but simply seem to have blanked out. I have an excel file with the following headers. Product|Version|Status|Date|Executive|Company Name|Contact| Note: All the rows under the headers can be repeated multiple times. Filter is an obvious method to get data fulfilling a criteria. But I would like to avoid use of "Filter" Is there any method wherein I can create a combo box on "Company Name" and get a list of all corresponding entries under the "Company Name" thus selected from the combo box. Further, is it possible to get only a unique list in the combo box but on selection, display all the rows and columns with the matching "Company Name"? This, is possible, would have to be done in a new sheet. Its ok with me as long as the combo box and display properties gets updated every time I update the list in the main worksheet. By the way, I am using Excel 2007. Any Help in this is appreciated. Thanks Ashish Pradhan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box and Multiple row / column display
Hi Ashly Start with creating a combobox in the spreadsheet Put the following 2 procedures in a standard module: Note where ever I use the Cells property change the 2nd argument as necessary for example say you want to filter by Column E, then edit like this: .Cells(2, 5) and .Cells(LastRow, 5) also rename the sheet as your sheet name ------------------------------------------- Sub LoadCombo() Dim Sht As Worksheet, Rng As Range, DataRng As Range Dim LastRow As Long, i As Long Dim Cb, Data() Set Sht = ThisWorkbook.Worksheets("Sheet1") With Sht Set Cb = Sheet1.ComboBox1 Cb.Clear LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row ReDim Data(2 To LastRow - 1) Set DataRng = .Range(.Cells(2, 1), .Cells(LastRow, 1)) For i = 2 To LastRow - 1 Data(i) = DataRng.Cells(i - 1) Next i SortData Data Cb.AddItem Data(LBound(Data)) For i = LBound(Data) + 1 To UBound(Data) Debug.Print Data(i) If Data(i) < Data(i - 1) Then Cb.AddItem Data(i) Next i End With End Sub -------------------------------------------------------------------------- Sub SortData(List()) Dim First As Integer, Last As Long Dim i As Long, x As Long Dim Temp First = LBound(List) Last = UBound(List) For i = First To Last - 1 For x = i + 1 To Last If List(i) List(x) Then Temp = List(x) List(x) = List(i) List(i) = Temp End If Next x Next i End Sub ------------------------------------------------------- Go in Design Mode in the sheet containing the combo and double click the combo and put the following code: Private Sub ComboBox1_Change() Dim Sht As Worksheet, Cb Dim LastRow As Long, i As Long Dim DataRng As Range Set Cb = Me.ComboBox1 Set Sht = ThisWorkbook.Worksheets("Sheet1") With Sht LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row Set DataRng = .Range(.Cells(1, 1), .Cells(LastRow, 1)) If Cb.Text = "" Then DataRng.AutoFilter Field:=1 Else DataRng.AutoFilter Field:=1, Criteria1:=Cb.Text End If End With End Sub Hope this helps let me know if i can be of any further assistance -- A. Ch. Eirinberg "Ashly" wrote: Hi Been racking my brains for a few days but simply seem to have blanked out. I have an excel file with the following headers. Product|Version|Status|Date|Executive|Company Name|Contact| Note: All the rows under the headers can be repeated multiple times. Filter is an obvious method to get data fulfilling a criteria. But I would like to avoid use of "Filter" Is there any method wherein I can create a combo box on "Company Name" and get a list of all corresponding entries under the "Company Name" thus selected from the combo box. Further, is it possible to get only a unique list in the combo box but on selection, display all the rows and columns with the matching "Company Name"? This, is possible, would have to be done in a new sheet. Its ok with me as long as the combo box and display properties gets updated every time I update the list in the main worksheet. By the way, I am using Excel 2007. Any Help in this is appreciated. Thanks Ashish Pradhan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box and Multiple row / column display
you can use the Advanced Filter to extract unique company named without too
much code paste this into a module. It assumes that your table starts at A1 Sub UniqueNames() Dim target As Range Set target = Range("A1").End(xlToRight).Offset(, 2) target = "Company Name" With Range("A1").CurrentRegion .AdvancedFilter xlFilterCopy, , target, True End With target.Sort target, Header:=xlYes End Sub method .... put the column header for the extracted list a few cells to the right of the table using A1 , the current region method returns the table of data, then the advanced filter extracts the company name to the added column header fetching unique values, and then we sort the resultant list easy hey ;) hope this helps. You already know how to point your combo box at this table I guess? "Howard31" wrote in message ... Hi Ashly Start with creating a combobox in the spreadsheet Put the following 2 procedures in a standard module: Note where ever I use the Cells property change the 2nd argument as necessary for example say you want to filter by Column E, then edit like this: .Cells(2, 5) and .Cells(LastRow, 5) also rename the sheet as your sheet name ------------------------------------------- Sub LoadCombo() Dim Sht As Worksheet, Rng As Range, DataRng As Range Dim LastRow As Long, i As Long Dim Cb, Data() Set Sht = ThisWorkbook.Worksheets("Sheet1") With Sht Set Cb = Sheet1.ComboBox1 Cb.Clear LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row ReDim Data(2 To LastRow - 1) Set DataRng = .Range(.Cells(2, 1), .Cells(LastRow, 1)) For i = 2 To LastRow - 1 Data(i) = DataRng.Cells(i - 1) Next i SortData Data Cb.AddItem Data(LBound(Data)) For i = LBound(Data) + 1 To UBound(Data) Debug.Print Data(i) If Data(i) < Data(i - 1) Then Cb.AddItem Data(i) Next i End With End Sub -------------------------------------------------------------------------- Sub SortData(List()) Dim First As Integer, Last As Long Dim i As Long, x As Long Dim Temp First = LBound(List) Last = UBound(List) For i = First To Last - 1 For x = i + 1 To Last If List(i) List(x) Then Temp = List(x) List(x) = List(i) List(i) = Temp End If Next x Next i End Sub ------------------------------------------------------- Go in Design Mode in the sheet containing the combo and double click the combo and put the following code: Private Sub ComboBox1_Change() Dim Sht As Worksheet, Cb Dim LastRow As Long, i As Long Dim DataRng As Range Set Cb = Me.ComboBox1 Set Sht = ThisWorkbook.Worksheets("Sheet1") With Sht LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row Set DataRng = .Range(.Cells(1, 1), .Cells(LastRow, 1)) If Cb.Text = "" Then DataRng.AutoFilter Field:=1 Else DataRng.AutoFilter Field:=1, Criteria1:=Cb.Text End If End With End Sub Hope this helps let me know if i can be of any further assistance -- A. Ch. Eirinberg "Ashly" wrote: Hi Been racking my brains for a few days but simply seem to have blanked out. I have an excel file with the following headers. Product|Version|Status|Date|Executive|Company Name|Contact| Note: All the rows under the headers can be repeated multiple times. Filter is an obvious method to get data fulfilling a criteria. But I would like to avoid use of "Filter" Is there any method wherein I can create a combo box on "Company Name" and get a list of all corresponding entries under the "Company Name" thus selected from the combo box. Further, is it possible to get only a unique list in the combo box but on selection, display all the rows and columns with the matching "Company Name"? This, is possible, would have to be done in a new sheet. Its ok with me as long as the combo box and display properties gets updated every time I update the list in the main worksheet. By the way, I am using Excel 2007. Any Help in this is appreciated. Thanks Ashish Pradhan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box Display Multiple Column Info After Selection Made | Excel Programming | |||
Combo Box Display | Excel Discussion (Misc queries) | |||
Can I pivot multiple data fields and display them in a column? | Excel Discussion (Misc queries) | |||
Display multiple lines of text within a cell from multiple column. | Excel Worksheet Functions | |||
Adding to Multiple Column combo Box | Excel Programming |