Add filter vba combox values on user form

[Lumiance]

Good afternoon,

I need some help with the following;
On a user form I have a combobox (cboBlockNo) filled with data (block numbers), this picks up all numbers in a sheet
I like to filter this combo box with (or not) with a checkbox (chkboxInOut) on the same user form.
When this checkbox is selected (TRUE) then the combobox should not been populated with data (block numbers) which is finished (TRUE) in column "BV" sheet "Blockage".
I currently have a working code to fill the combobox, but it will be filled with all records, now I like to by changing the checkbox to include or exclude finished records.
Only I am not sure (do not know) how to include this in below code.

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
   
   Dim Style As Long, Menu As Long
   hWndForm = FindWindow("ThunderDFrame", Me.Caption)
   Style = GetWindowLong(hWndForm, &HFFF0)
   Style = Style And Not &HC00000
   SetWindowLong hWndForm, &HFFF0, Style
   DrawMenuBar hWndForm
      
    Dim Cell        As Range
    Dim col         As Variant
    Dim Descending  As Boolean
    Dim Entries     As Collection
    Dim Items       As Variant
    Dim index       As Long
    Dim j           As Long
    Dim RngBeg      As Range
    Dim RngEnd      As Range
    Dim row         As Long
    Dim Sorted      As Boolean
    Dim temp        As Variant
    Dim test        As Variant
    Dim Wks         As Worksheet
    
'CHECK
    
        Set Wks = ThisWorkbook.Worksheets("Blockages")
        Set RngBeg = Wks.Range("A2")
        col = RngBeg.Column
        
        Set RngEnd = Wks.Cells(Rows.Count, col).End(xlUp)
        
            Set Entries = New Collection
            ReDim Items(0)
            
            For row = RngBeg.row To RngEnd.row
                Set Cell = Wks.Cells(row, col)
                    On Error Resume Next
                        test = Entries(Cell.Text)
                        If err = 5 Then
                            Entries.Add index, Cell.Text
                            Items(index) = Cell.Text
                            index = index + 1
                            ReDim Preserve Items(index)
                        End If
                    On Error GoTo 0
            Next row
              
        index = index - 1
        Descending = True  ' Set this to True to sort in descending order Z-A.
        ReDim Preserve Items(index)
        
            Do
                Sorted = True
                
                For j = 0 To index - 1
                    If Descending Xor StrComp(Items(j), Items(j + 1), vbTextCompare) = 1 Then
                        temp = Items(j + 1)
                        Items(j + 1) = Items(j)
                        Items(j) = temp
                        
                        Sorted = False
                    End If
                Next j
                
                index = index - 1
            Loop Until Sorted Or index < 1
                   
cboBlockNo.List = Items
Me.MultiPage1.Value = 0
Wks.Activate
Application.ScreenUpdating = True
End Sub

Thank you for your guidance!
Kind regards, Eric

 

[DanteAmor]

Welcome to MrExcel

Try this

Private Sub chkboxInOut_Click()
  Call PopulateCombo
End Sub

Private Sub UserForm_Initialize()
  Application.ScreenUpdating = False
  
  Dim Style As Long, Menu As Long
  hWndForm = FindWindow("ThunderDFrame", Me.Caption)
  Style = GetWindowLong(hWndForm, &HFFF0)
  Style = Style And Not &HC00000
  SetWindowLong hWndForm, &HFFF0, Style
  DrawMenuBar hWndForm
  
  Call PopulateCombo
End Sub

Sub PopulateCombo()
  Dim Cell        As Range
  Dim col         As Variant
  Dim Descending  As Boolean
  Dim Entries     As Collection
  Dim Items       As Variant
  Dim index       As Long
  Dim j           As Long
  Dim RngBeg      As Range
  Dim RngEnd      As Range
  Dim row         As Long
  Dim Sorted      As Boolean
  Dim temp        As Variant
  Dim test        As Variant
  Dim Wks         As Worksheet
  Dim charge      As Boolean
  
  'CHECK
  Set Wks = ThisWorkbook.Worksheets("Blockages")
  Set RngBeg = Wks.Range("A2")
  col = RngBeg.Column
  
  Set RngEnd = Wks.Cells(Rows.Count, col).End(xlUp)
  
  Set Entries = New Collection
  ReDim Items(0)
  
  For row = RngBeg.row To RngEnd.row
    charge = True
    Set Cell = Wks.Cells(row, col)
    If chkboxInOut = True Then
      If Wks.Cells(row, "BV").Value = True Then
        charge = False
      End If
    End If
    If charge = True Then
      On Error Resume Next
      test = Entries(Cell.Text)
      If Err = 5 Then
        Entries.Add index, Cell.Text
        Items(index) = Cell.Text
        index = index + 1
        ReDim Preserve Items(index)
      End If
      On Error GoTo 0
    End If
  Next row
  
  index = index - 1
  Descending = True  ' Set this to True to sort in descending order Z-A.
  ReDim Preserve Items(index)
  
  Do
    Sorted = True
    For j = 0 To index - 1
      If Descending Xor StrComp(Items(j), Items(j + 1), vbTextCompare) = 1 Then
      temp = Items(j + 1)
      Items(j + 1) = Items(j)
      Items(j) = temp
      Sorted = False
      End If
    Next j
    index = index - 1
  Loop Until Sorted Or index < 1
  
  cboBlockNo.List = Items
  Me.MultiPage1.Value = 0
  Wks.Activate
  Application.ScreenUpdating = True
End Sub

 

[Lumiance]

Superb ... works like a charm.. many thanks Dante Amor ?

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.