Pass the value from autofilter to a combox userform in vba

Question

can we pass the value by applying autofilter to a particular coloumn and pass the filter value to a combox userform in excel vba

if it is possible can you please provide the generalized code for the same

EDIT: Code from OP's comment:

Selection.AutoFilter 
Range("A:A").Select 
ActiveSheet.Range("A1:AL1000").AutoFilter Field:=1, Criteria1:="" & TextBox1.Text & "", Operator:=xlAnd 
Range("b1").Select 
Range("B:B").Copy 
'ActiveCell.CurrentRegion.Select 
'ActiveCell.CurrentRegion.Copy 
Sheets("Data").Select 
Range("B2").Select 
ActiveSheet.Paste

Yes it is possible and has been covered earlier if my memory is not failing me. Regarding providing you the code, it doesn't work like this here in stackoverflow. Please show us what have you tried? Questions asking for code must demonstrate a minimal understanding of the problem being solved.Include attempted solutions, why they didn't work, and the expected results. See also: [Stack Overflow question checklist](http://meta.stackexchange.com/questions/156810/stack-overflow-question-checklist) — Siddharth Rout, Dec 20 '13 at 20:49
currently this code taking the input from user and pasting them in a new worksheet ..I want the filtered data should come in a combox userform — pragya, Dec 20 '13 at 21:03
yes the filtered value of coloumn b should come in a combobox.. can this value be showed in a combobox — pragya, Dec 20 '13 at 21:10
This code is filtering the values but the filtered values are not coming in combo box .. ComboBox1.AddItem aCell.Value this line is giving error i have posted the code below .. — pragya, Dec 21 '13 at 08:56

score 0 · Answer 1 · edited May 23 '17 at 11:50

Is this what you are trying? I have commented the code so you shouldn't have a problem in understanding the code. But if you do then simply post back.

Option Explicit

Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim copyFrom As Range, aCell As Range
    Dim lRow As Long
    Dim strSearch As String

    Set wb = ThisWorkbook
    '~~> Set this to the worksheet where the autofilter is applied
    Set ws = wb.Worksheets("yourSheetName")

    '~~> Filter Column on
    strSearch = TextBox1.Text

    With ws
        '~~> Remove any filters
        .AutoFilterMode = False

        With .Range("A1:AL1000")
            .AutoFilter Field:=1, Criteria1:="=" & strSearch

            '~~> Identify the filtered range
            Set copyFrom = .Offset(1, 1).SpecialCells(xlCellTypeVisible)

            '~~> Add filtered values from Column 2 i.e from
            '~~> Col B to Combobox
            For Each aCell In copyFrom
                If aCell.Column = 2 Then _
                ComboBox1.AddItem aCell.Value
            Next
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

Tip: You should avoid the use of .Select/.Activate You may wanna see THIS

pragya · Answer 2 · 2013-12-21T05:49:09.507

Private Sub CommandButton2_Click()

Dim wb As Workbook
Dim ws As Worksheet
Dim copyFrom As Range, aCell As Range
Dim lRow As Long
Dim strSearch As String

Set wb = ThisWorkbook
'~~> Set this to the worksheet where the autofilter is applied
Set ws = wb.Worksheets("MT")



With ws
    '~~> Remove any filters
    .AutoFilterMode = False

    With .Range("A1:AL1000")
        .AutoFilter Field:=1, Criteria1:="*" & TextBox1.Text & "*", Operator:=xlAnd

        '~~> Identify the filtered range
        Set copyFrom = .Offset(1, 1).SpecialCells(xlCellTypeVisible)

        '~~> Add filtered values from Column 2 i.e from
        '~~> Col B to Combobox
        For Each aCell In copyFrom
            If aCell.Column = 2 Then _
            ComboBox1.AddItem aCell.Value
        Next
    End With

    '~~> Remove any filters
    .AutoFilterMode = False
End With

End Sub

This code is filtering the values but the filtered values are not coming in combo box .. ComboBox1.AddItem aCell.Value this line is giving error

This code is filtering the values but the filtered values are not coming in combo box .. ComboBox1.AddItem aCell.Value this line is giving error — pragya, Dec 21 '13 at 06:04

Pass the value from autofilter to a combox userform in vba

2 Answers2