PDA

View Full Version : Solved: Filter Combo Box Value



brorick
05-24-2008, 10:31 PM
Can anyone tell me how to filter the range my combo box is based on. I have 3 columns in my range. The second column in my range is titled Department. I would like to filter my combo box value based on Department = "Sales". Does anyone have any ideas. :think:


Dim irng As Range
With Worksheets("frmProductInfo")
Set irng = .Range("ItemsInfo")
cboItem.List = irng.Resize(irng.Rows.Count - 1).Offset(1).Value
End With


I tried to incorporate the following, but with no success.

Range("ItemsInfo").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:="Sales", Unique:=False

Thank you in advance.

mikerickson
05-24-2008, 11:52 PM
This code assumes that there is a named range CritRange for the AdvancedFilter's criteria range. (This example uses a typical 2 row X 1 col crit. range)
Private Sub UserForm_Initialize()
Call FilterToCBOitem("Name", "John Smith")
End Sub

Sub FilterToCBOitem(Optional headerString As String, Optional critValue As String)
Dim filteredRange As Range, oneArea As Range

Rem set criteria range
If headerString = vbNullString Then headerString = CStr(Range("iteminfo").Range("a1").Value)
With Range("CritRange")
.Range("a1").Value = headerString
.Range("a2").Value = critValue
End With

Rem filter ItemInfo
With Range("ItemInfo")
.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("CritRange"), Unique:=True
Set filteredRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
End With

Rem load combo box
With Me.cboItem
.ColumnCount = filteredRange.Columns.Count
For Each oneArea In filteredRange.Rows
If 0 < Application.CountA(oneArea) Then
.AddItem oneArea.Range("a1").Value
.List(.ListCount - 1, 1) = oneArea.Range("B1").Value
.List(.ListCount - 1, 2) = oneArea.Range("c1").Value
End If
Next oneArea
.ColumnWidths = "40;0;40": Rem show columns 1 and 3 , not 2
.TextColumn = 2
End With

End SubThe sub could be used if the userform needs to change the filter.

brorick
05-25-2008, 12:03 AM
Mikerickson, thank you once again. I will give it a try.

brorick
05-25-2008, 01:20 AM
Mikerickson. It worked like a charm. To the rescue twice in one day. I appreciate the help. :thumb

mikerickson
05-25-2008, 08:43 AM
You're welcome.