advanced filter by optionbuttons & combobox

[abdelfattah]

hi, all
actually i have a problem when press on of the option button (CODE) OR (ORHIGIN) OR (BRAND )and choose from combobox it doesn't filter data correctly it just filter in row1 and hide the rest of data for instance if i press code and choose from combobox aa1 it just filter one row but it supposes two row and if i press origin it 's filtering wrong just aa1
this is main sheet & userform

this is some results

this when i choose the lastrow in combobox stay filter first row

my codes

Private Sub ComboBox1_Click()
Dim I As Long
If Me.OptionButton1.Value = True Then
SHEET1.Range("A2").Value = Me.ComboBox1.Value
ElseIf Me.OptionButton2.Value = True Then
SHEET1.Range("B2").Value = Me.ComboBox1.Value
ElseIf Me.OptionButton3.Value = True Then
SHEET1.Range("C2").Value = Me.ComboBox1.Value
Else
SHEET1.Range("D2").Value = Me.ComboBox1.Value
End If
On Error Resume Next
ActiveSheet.ShowAllData
I = SHEET1.Range("A100000").End(xlUp).Offset(1, 0).Row
SHEET1.Range("A" & 2, "E" & I).AdvancedFilter xlFilterInPlace, SHEET1.Range("A2:E3")

End Sub

Private Sub CommandButton1_Click()
ActiveSheet.ShowAllData
End Sub

Private Sub OptionButton1_Click()
Me.ComboBox1.BoundColumn = 1
End Sub

Private Sub OptionButton2_Click()
Me.ComboBox1.BoundColumn = 2
End Sub

Private Sub OptionButton3_Click()
Me.ComboBox1.BoundColumn = 3
End Sub

Private Sub OptionButton4_Click()
Me.ComboBox1.BoundColumn = 4
End Sub

Private Sub UserForm_Initialize()
Dim r As Range
With Worksheets("Sheet1")
        Set r = .Range("A2", .Range("E65536").End(xlUp))
End With

ComboBox1.RowSource = "Sheet1!" & r.Address
End Sub

 

[dmt32]

Hi,
try replacing your codes with following & see if will do what you want

Dim rng As Range
Private Sub ComboBox1_Change()
    Dim i As Integer
    Dim FilterValue As String
    Dim ws As Worksheet
   
    Set ws = rng.Parent
    FilterValue = Me.ComboBox1.Value
   
    For i = 1 To 4
        If Me.Controls("OptionButton" & i).Value Then
'create filter
            ws.Cells(1, 10).Value = ws.Cells(1, i).Value
            ws.Cells(2, 10).Formula = "=" & """=" & FilterValue & """"
            Exit For
        End If
    Next
  
    On Error Resume Next
    If ws.FilterMode Then ws.ShowAllData
    If Len(FilterValue) > 0 Then rng.AdvancedFilter xlFilterInPlace, CriteriaRange:=ws.Range("J1:J2")
End Sub

Sub SetComBoBox(ByVal Index As Integer)
    With Me.ComboBox1
        .BoundColumn = Index
        .TextColumn = Index
        .ListIndex = -1
    End With
End Sub
Private Sub OptionButton1_Click()
    SetComBoBox 1
End Sub
Private Sub OptionButton2_Click()
    SetComBoBox 2
End Sub
Private Sub OptionButton3_Click()
    SetComBoBox 3
End Sub
Private Sub OptionButton4_Click()
    SetComBoBox 4
End Sub

Private Sub UserForm_Initialize()

    Set rng = Worksheets("Sheet1").Range("A1").CurrentRegion
   
     With Me.ComboBox1
        .RowSource = ""
        .ColumnCount = 4
        .List = rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Value
     End With
     Me.OptionButton1.Value = True
End Sub

Note: variable rng which must sit up TOP of your userforms code page.

Hope Helpful

Dave

 

[abdelfattah]

awesome your codes thanks so much for your assistance this is what i'look for but i have problem when i run the form an i change option button from code to brand or origin or brand or type it gives me error run time error 94 in this line FilterValue = Me.ComboBox1.Value

 

[dmt32]

awesome your codes thanks so much for your assistance this is what i'look for but i have problem when i run the form an i change option button from code to brand or origin or brand or type it gives me error run time error 94 in this line FilterValue = Me.ComboBox1.Value

try changing that line with following

FilterValue = Me.ComboBox1.Text

and see if resolves

Dave

 

[abdelfattah]

yes, it works excellently, thanks for every thing

take care

 

[dmt32]

yes, it works excellently, thanks for every thing

take care

welcome - glad solution helped

Dave