Populate a listbox if it is not already populated?

KateD

New Member
Joined
Sep 15, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hello,
This forum has helped my so much, but today I cannot find an answer to my problem so here is my first post. I hope i am giving enough information.
I have a user form with combo boxes and list boxes to filter a database in excel. The results are shown live in a list box below. They first search for the sector of activity, then the sector, then the sub-sector. Each is dynamically populated based on the previous combo box. The final box is what satellite of our company it is closest to. This is also dynamically populated based on the previous list box. However, I also need people to use this box, if they wish, without selecting the others.

How do I dynamically populate a list box but have an "if previous listboxes not selected, then populate from..." It feels like it should be in the initialize section?

Here is my code for the previous listbox change that then pouplates my "proche" list box. It is the "proche" listbox that needs to be populated in two ways.

VBA Code:
Private Sub SouSectLB_Change()

  Application.ScreenUpdating = False
 
    Dim ws As Worksheet
    Dim wsTemp As Worksheet
    Dim rng As Range
    Dim rnglr As Long
    Dim numRows As Long

    rnglr = ThisWorkbook.Sheets("données").Cells(Rows.Count, "A").End(xlUp).Row
  
    Dim i As Long, j As Long, rw As Long
    Dim Myarray() As String

  
    Set ws = Sheets("données")
    Set wsTemp = Sheets("temp")
 

    ws.AutoFilterMode = False
   

 
    If sect.ListIndex = -1 Then Exit Sub
   
     Dim sFilters As String

   
    For i = 0 To SouSectLB.ListCount - 1
        'If value is selected, add to sFilters variable
        If SouSectLB.Selected(i) Then sFilters = sFilters & "|" & SouSectLB.List(i)
    Next i


    With ws
     
        .AutoFilterMode = False
       
    
       If Len(sFilters) > 0 Then
        With .Range("A1:O1")
        .AutoFilter Field:=11, Criteria1:=SectAct.Value
        .AutoFilter Field:=12, Criteria1:=sect.Value
        .AutoFilter 13, Split(sFilters, "|"), xlFilterValues _
        , Operator:=xlAnd
        End With
        Else:
        With .Range("A1:O1")
        .AutoFilter Field:=11, Criteria1:=SectAct.Value
        .AutoFilter Field:=12, Criteria1:=sect.Value _
        , Operator:=xlAnd
        End With
        End If
    End With
   
   
    On Error Resume Next
    Set rng = ws.Range("A1:O" & rnglr).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
   
    ThisWorkbook.Sheets("temp").Cells.Clear
    rng.Copy
    ThisWorkbook.Sheets("temp").Range("A1").PasteSpecial

    numRows = ThisWorkbook.Sheets("temp").Cells(Rows.Count, "A").End(xlUp).Row
     
    Set rng = ThisWorkbook.Sheets("temp").Range("A1:O" & rnglr)
     
    With Me.ListBox1
        .Clear
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count

        ReDim Myarray(numRows, rng.Columns.Count)

        rw = 0

        For i = 1 To numRows
            For j = 0 To rng.Columns.Count
                Myarray(rw, j) = rng.Cells(i, j + 1)
            Next
            rw = rw + 1
        Next

        .List = Myarray

    End With
   
    Dim lrow As Long
    Dim ii As Long
    Dim colproche As New Collection
    Dim itmproche As Variant
   
    Me.proche.Clear
   
   
    With wsTemp
     
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
       
    
        On Error Resume Next
        For ii = 2 To lrow
            colproche.Add .Range("N" & ii).Value2, CStr(.Range("N" & ii).Value2)
        Next ii
        On Error GoTo 0
       
   
        For Each itmproche In colproche
            proche.AddItem itmproche
        Next itmproche
    End With
 
End Sub
 
Last edited by a moderator:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,268
My suggestion is that you slightly change your approach as follows:
-When you open the userform you populate all including "proche". I understand that "proche" is a filetered subset of your database; since at this time no filter has yet been set, "proche" will be initialized with the full database.
-When each of the controls used to set a filter condition changes then "proche" is repopulated using the available filters

In this way "proche" is always ready with the latest filtered list

OR
-you keep the current approach but add a CommandButton that populates "proche" using the filters that are currently set

Bye
 
Solution

KateD

New Member
Joined
Sep 15, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Thank you. As soon as i read it i thought "of course!" :0 I always assume first that it has to be complex!
The first option is perfect. Thanks again
 

Forum statistics

Threads
1,147,568
Messages
5,741,874
Members
423,692
Latest member
Bhanu1988

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top