CheckBox on UserForm to show Full list or Part list according to Criteria

[10shlomi10]

Good Day,

I wish to be able to show on ListBox , which I have on UserForm, all list OR part of the list, depending on CheckBox Click.
Meaning, I have a CheckBox which will be True/Checked when UserForm is load, and then ListBox will show only Row from data that has #N/A on specific column (On attached example it's column C) BUT Unique on other column, let say column A.
ColumnA | ColumnB
aa | #N/A
aa | #N/A
aa | #N/A
bb | #N/A
bb | #N/A

will show on ListBox:
aa |#N/A
bb |#N/A
etc'....

When the UserForm is already on screen, I want the ability to Un-Check the CheckBox and then the ListBox will be fill up with ALL data.

any help will be appreciated.

example File is on attached.

All the Best !

[mikerickson]

I think this might do what you want

Option Explicit
' http://dailydoseofexcel.com/archives/2004/05/10/populating-multi-column-listboxcombobox/

Private Sub CheckBox1_Click()
    Call fillListBox
End Sub

Private Sub ListBox1_Click()
    If TypeName(ListBox1.List(ListBox1.ListIndex, 2)) = "Error" Then
        MsgBox CStr(ListBox1.List(ListBox1.ListIndex, 2)) = CStr(xlErrNA)
    End If
End Sub

Private Sub UserForm_Initialize()
  
    CheckBox1 = False
    CheckBox1.Caption = "Show  only N/A"
    
    Call fillListBox
    
End Sub
     
Sub fillListBox()
    Dim i As Long, j As Long
    With ListBox1
        .List = DataRange.Value
        
        If CheckBox1.Value Then
            For i = .ListCount - 1 To 0 Step -1
                If Not IsError(.List(i, 2)) Then
                    .RemoveItem i
                End If
            Next i
        End If
        
        For i = 0 To .ListCount - 1
            For j = 0 To .ColumnCount - 1
                If IsError(.List(i, j)) Then .List(i, j) = "#N/A"
            Next j
        Next i
    End With
End Sub

Function DataRange() As Range
    With ThisWorkbook.Sheets("Sheet1").Range("A2")
        Set DataRange = Range(.Cells(1, ListBox1.ColumnCount), .End(xlDown))
     End With
End Function

I removed the toggling of the checkbox caption, since it didn't make sense to me. That feature would be more understandable in a Toggle Button or a Command button, IMO.

[Kenneth Hobs]

I like the way that you added NA Mike.

To address the duplicates issue, I used this approach.

Private Sub UserForm_Initialize()  
  CheckBox1 = False
  CheckBox1.Caption = "Show ALL"
  AllList
End Sub


Private Sub CheckBox1_Click()
  If CheckBox1.Caption = "Show #N/A" Then
    AllList
    CheckBox1.Caption = "Show ALL"
    ElseIf CheckBox1.Caption = "Show ALL" Then
      NAlist
      CheckBox1.Caption = "Show #N/A"
  End If
End Sub
     
Sub AllList()
  Dim i&, j&
  With ListBox1
    .List = Sheet1.Range("A2", Sheet1.Range("A2").End(xlDown)).Resize(, .ColumnCount).Value
    For i = 0 To .ListCount - 1
      For j = 0 To .ColumnCount - 1
        If IsError(.List(i, j)) Then .List(i, j) = "#N/A"
      Next j
    Next i
    End With
End Sub
     
Sub NAlist()
  Dim i&, j&
  With Sheet2
    .Range("D1").Value = Sheet1.Range("C1").Value
    .Range("D2").Formula = "=NA()"
    Sheet1.UsedRange.Resize(, 3).AdvancedFilter Action:=xlFilterInPlace, _
      CriteriaRange:=.Range("D1:D2"), Unique:=True
    Sheet1.Range("A2", Sheet1.Range("A2").End(xlDown)).Resize(, 3).SpecialCells(xlCellTypeVisible).Copy
    .Range("A1").PasteSpecial xlPasteValues
    ListBox1.List = .UsedRange.Value
    Sheet1.ShowAllData
    .UsedRange.Clear
  End With
  
  With ListBox1
    For i = 0 To .ListCount - 1
      For j = 0 To .ColumnCount - 1
          If IsError(.List(i, j)) Then .List(i, j) = "#N/A"
      Next j
    Next i
  End With
End Sub

[mikerickson]

On my Mac this line fills the listbox with everything, including the hidden rows.

ListBox1.List = .UsedRange.Value

I'd have to use code like this to removed duplicates from the listbox.

Sub RemoveDuplicates(aListBox As MSForms.ListBox)
    Dim i As Long, j As Long
    Dim strTest As String, strExists As String
    Dim Delimiter1 As String, Delimiter2 As String
    Delimiter1 = Chr(5)
    Delimiter2 = Chr(6)
    
    strExists = Delimiter2
    
    With aListBox
        For i = .ListCount - 1 To 0 Step -1

            strTest = vbNullString
            For j = 0 To .ColumnCount - 1
                strTest = strTest & Delimiter1 & .List(i, j)
            Next j
            
            If InStr(1, strExists, strTest & Delimiter2) = 0 Then
                strExists = strExists & strTest & Delimiter2
            Else
                .RemoveItem i
            End If

        Next i
    End With
End Sub

[Kenneth Hobs]

That is weird Mike. There should be no hidden rows in sheet2 where the sheet1 visible usedrange rows were copied. Obviously, the AllList lists all. I normally would remove the duplicates from the AllList as well.

Another reason I chose to put the filtered data into sheet2 was so that autofilter could sort the data if needed.

I just posted my method since I was thinking about this thread before your posts.

[mikerickson]

I didn't read carefully, you were copying to the other location.

[10shlomi10]

Dear Kenneth
Dear mike

Many thanks for your assistance.
it was really a great help to me.

All the Best !