Filter Status Using Checkbox

[iggyboy29]

Hi everyone can anyone help me to put code in checkbox to filter a list box based on check box is check ,for example if i uncheck DONE, i wish the DONE is hide in the list box

HERES MY CODE

Private Sub ComboBox1_AfterUpdate()
Call Data_Filter
End Sub

Private Sub ComboBox1_Change()
Call Data_Filter
End Sub

Private Sub UserForm_Initialize()

Dim lr As Long
lr = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox1
    .ColumnCount = 2
    .ColumnHeads = True
    .ColumnWidths = "60,60"
    .RowSource = "Sheet1!A2:B" & lr
End With

    Call Combobox_List

End Sub

Sub Combobox_List()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DROPDOWN")

Dim i As Integer

Me.ComboBox1.Clear
Me.ComboBox1.AddItem ""

For i = 1 To Application.WorksheetFunction.CountA(sh.Range("A:A"))
    Me.ComboBox1.AddItem sh.Range("A" & i)
Next i

End Sub

Sub Data_Filter()
Application.ScreenUpdating = False

Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Sheet1")

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet3")

dsh.AutoFilterMode = False
sh.AutoFilterMode = False
sh.UsedRange.ClearContents

'''''' AUTO FILTER ''''''

If Me.ComboBox1.Value <> "" Then
    dsh.UsedRange.AutoFilter 1, Me.ComboBox1.Value
End If

'If Me.CheckBox1.Value = True Then
 '   dsh.UsedRange.AutoFilter 2, "DONE"
'End If

'If Me.CheckBox2.Value = True Then
    'dsh.UsedRange.AutoFilter 2, "ON GOING"
'End If

'If Me.CheckBox3.Value = True Then
    'dsh.UsedRange.AutoFilter 2, "NOT YET"
'End If

dsh.UsedRange.Copy
sh.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

dsh.AutoFilterMode = False

'''''' DISPLAY DATA IN LIST BOX ''''''

Dim lr As Long
lr = Application.WorksheetFunction.CountA(Sheet3.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox1
    .ColumnCount = 2
    .ColumnHeads = True
    .ColumnWidths = "60,60"
    .RowSource = "Sheet3!A2:B" & lr
End With

End Sub

 

[iggyboy29]

Hi everyone can anyone help me to put code in checkbox to filter a list box based on check box is check ,for example if i uncheck DONE, i wish the DONE is hide in the list box

HERES MY CODE

Private Sub ComboBox1_AfterUpdate()
Call Data_Filter
End Sub

Private Sub ComboBox1_Change()
Call Data_Filter
End Sub

Private Sub UserForm_Initialize()

Dim lr As Long
lr = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox1
    .ColumnCount = 2
    .ColumnHeads = True
    .ColumnWidths = "60,60"
    .RowSource = "Sheet1!A2:B" & lr
End With

    Call Combobox_List

End Sub

Sub Combobox_List()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DROPDOWN")

Dim i As Integer

Me.ComboBox1.Clear
Me.ComboBox1.AddItem ""

For i = 1 To Application.WorksheetFunction.CountA(sh.Range("A:A"))
    Me.ComboBox1.AddItem sh.Range("A" & i)
Next i

End Sub

Sub Data_Filter()
Application.ScreenUpdating = False

Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Sheet1")

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet3")

dsh.AutoFilterMode = False
sh.AutoFilterMode = False
sh.UsedRange.ClearContents

'''''' AUTO FILTER ''''''

If Me.ComboBox1.Value <> "" Then
    dsh.UsedRange.AutoFilter 1, Me.ComboBox1.Value
End If

'If Me.CheckBox1.Value = True Then
 '   dsh.UsedRange.AutoFilter 2, "DONE"
'End If

'If Me.CheckBox2.Value = True Then
    'dsh.UsedRange.AutoFilter 2, "ON GOING"
'End If

'If Me.CheckBox3.Value = True Then
    'dsh.UsedRange.AutoFilter 2, "NOT YET"
'End If

dsh.UsedRange.Copy
sh.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

dsh.AutoFilterMode = False

'''''' DISPLAY DATA IN LIST BOX ''''''

Dim lr As Long
lr = Application.WorksheetFunction.CountA(Sheet3.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox1
    .ColumnCount = 2
    .ColumnHeads = True
    .ColumnWidths = "60,60"
    .RowSource = "Sheet3!A2:B" & lr
End With

End Sub

Filter.xlsm - Shared via TeraBox

TeraBox offers up to 1024GB of free cloud storage. Download the app for secure storage and file sharing anytime, anywhere.

terabox.com