Combo Box and Multiple row / column display

[Ashly]

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

 

[Howard31]

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

 

[Patrick Molloy]

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?