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 !