Listboxes with Checkboxes to filter data

Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
-
Hello,

Greetings!

I have a query regarding making dashboard. I have a data sheet where all data is managed. I want slicer with listbox having check boxes to filter the data based on the multi selection from listbox. I have 09 listbox. When I select Company Name or Names from listbox, 2nd list box display the checkbox list based on selection of Parent listbox and so on. By default, all checkbox must be marked or true and I need Select All / Unselect All checkbox as well and data to be filter based on selections. All listbox must be auto update based on data.

File Link
https://we.tl/t-ALDJiYs9uK


Regards,
Naeem



System Configuration: Windows / Chrome 74.0.3729.131
See more 

4 replies

Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1572
0
Thank you
There is no listbox with checkboxes in VBA.
Game Start Now
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6 -
Please draw ActiveX Controls (ListBox) then in property window, please change the ListStyle property to (1 - fmListStyleOption) and in last, change MultiSelect property to (1 - fmMultiSelectMulti).... you will have checkboxes in listbox
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1572 -
What a great feature catch!

So you know what you can do with it, so do it!

I am confused by the fact that you can tweek properties of an element, but cannot loop through all elements upon a change of logic to another. For each....
Game Start Now
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6 -
I can't create such macro that's why I am here for help
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1572 -
It isnt a macro. It is code! Do you know how to perform a for loop?
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1572 -
For x = 1 to 9
With userform99
.controls ("mybox" & x).value = true
End with
Next

Or

For n =0 to listbox1.listcount -1
Listbox1.selected (n)=true
Next

There you have it. The code assumes ypu have a userform named userform99, your checkboxes are named mybox1, mybox2.

Have fun with that. This is a great start. Have a go!
Respond to ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1572
0
Thank you
I will take it one step further.....

Sub checkbox1_click ()
Select_all
End sub

Sub select_all ()
For n=0 to listbox1.listcount -1
Listbox.selected (n)=true
Next
End sub


Now all you have to do is trap whether the checkbox1 is already true, and deselect all of them all.


Have fun
Respond to ac3mark
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
0
Thank you
Hi GSN,

The way I set this up is that the listboxes control the slicers, which filter the data. The visible data will then be copied to the third sheet, where each column of data will be given a name and that named range is then used to repopulate the listboxes.

These are the steps required to set this up:

1. Create a "Table" for the data on the 'Data' sheet. The "Table" option can be found on the "Insert" ribbon.

2. Create "Slicers" for all the columns you have listboxes for. Move the slicers out of sight The "Slicer" option can also be found on the "Insert" ribbon.

3. Clear "Sheet3" and paste the data below in A1 (which should populate A1:I3):

Company Department Sub-Department Location Employee Status Employee Category Job Category Qualification Levels Religion
None None None None None None None None None
All All All All All All All All All

So the 1st row is used for the slicer names, the 2nd row for None and the 3rd for All.

4. On 'Sheet3' select A2:A3 and give it the name "Company". Type the name in the field on the top left where is says either A2 or A3.

5. Change the "ListFillRange" value of "Listbox1" into "Company". On the "Development" ribbon, click on the "Design mode" button. Right-click "Listbox1" and select "properties". Here you can find "ListFillRange". Don't forget to exit "Design mode".

6. Time to implement some code. Which I will paste at the bottom to keep the "steps" together.

7. Click on "All" on the 1st listbox to populate the other listboxes, then click on "All" on the 2nd listbox to populate the 1st listbox.

Now you are all set to play around and see if it is to your liking. What do you like and what not? The All/None options are a bit tricky.

Best regards,
Trowa


Code for sheet 'HR Dashboard':
Public pLoop As Boolean

Private Sub ListBox1_Change()
If pLoop = False Then
    pLoop = True
    Listbox_1
End If
End Sub

Private Sub ListBox2_Change()
If pLoop = False Then
    pLoop = True
    Listbox_2
End If
End Sub

Private Sub ListBox3_Change()
If pLoop = False Then
    pLoop = True
    Listbox_3
End If
End Sub

Private Sub ListBox4_Change()
If pLoop = False Then
    pLoop = True
    Listbox_4
End If
End Sub

Private Sub ListBox5_Change()
If pLoop = False Then
    pLoop = True
    Listbox_5
End If
End Sub

Private Sub ListBox6_Change()
If pLoop = False Then
    pLoop = True
    Listbox_6
End If
End Sub

Private Sub ListBox7_Change()
If pLoop = False Then
    pLoop = True
    Listbox_7
End If
End Sub

Private Sub ListBox8_Change()
If pLoop = False Then
    pLoop = True
    Listbox_8
End If
End Sub

Private Sub ListBox9_Change()
If pLoop = False Then
    pLoop = True
    Listbox_9
End If
End Sub


Code for sheet 'Sheet3':
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Integer
Application.EnableEvents = False

If Not Intersect(Target, Columns("A")) Is Nothing Then
    Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
    Range("A2:A" & lRow).Name = "Company"
ElseIf Not Intersect(Target, Columns("B")) Is Nothing Then
    Columns("B").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Sheets("Sheet3").Range("B" & Rows.Count).End(xlUp).Row
    Range("B2:B" & lRow).Name = "Department"
ElseIf Not Intersect(Target, Columns("C")) Is Nothing Then
    Columns("C").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Sheets("Sheet3").Range("C" & Rows.Count).End(xlUp).Row
    Range("C2:C" & lRow).Name = "Sub_Department"
ElseIf Not Intersect(Target, Columns("D")) Is Nothing Then
    Columns("D").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Sheets("Sheet3").Range("D" & Rows.Count).End(xlUp).Row
    Range("D2:D" & lRow).Name = "Location"
ElseIf Not Intersect(Target, Columns("E")) Is Nothing Then
    Columns("E").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Sheets("Sheet3").Range("E" & Rows.Count).End(xlUp).Row
    Range("E2:E" & lRow).Name = "Employee_Status"
ElseIf Not Intersect(Target, Columns("F")) Is Nothing Then
    Columns("F").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Sheets("Sheet3").Range("F" & Rows.Count).End(xlUp).Row
    Range("F2:F" & lRow).Name = "Employee_Category"
ElseIf Not Intersect(Target, Columns("G")) Is Nothing Then
    Columns("G").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Sheets("Sheet3").Range("G" & Rows.Count).End(xlUp).Row
    Range("G2:G" & lRow).Name = "Job_Category"
ElseIf Not Intersect(Target, Columns("H")) Is Nothing Then
    Columns("H").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Sheets("Sheet3").Range("H" & Rows.Count).End(xlUp).Row
    Range("H2:H" & lRow).Name = "Qualification_levels"
ElseIf Not Intersect(Target, Columns("I")) Is Nothing Then
    Columns("I").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Sheets("Sheet3").Range("I" & Rows.Count).End(xlUp).Row
    Range("I2:I" & lRow).Name = "Religion"
Else
    Exit Sub
End If

Cells.Interior.Pattern = xlNone

Application.EnableEvents = True
End Sub


Insert a module and paste the following in it:
Dim x, lCount As Integer
Dim laNone1, laAll1, laNone2, laAll2, laNone3, laAll3, laNone4, laAll4, laNone5, laAll5, _
laNone6, laAll6, laNone7, laAll7, laNone8, laAll8, laNone9, laAll9 As Boolean

Sub Listbox_1()
lCount = ActiveSheet.ListBox1.ListCount - 1

If ActiveSheet.ListBox1.Selected(0) = True And laNone1 = False Then
    laNone1 = True
    laAll1 = False
    ActiveSheet.ListBox1.Selected(1) = False
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox1.Selected(x) = False
        ActiveWorkbook.SlicerCaches("Slicer_Company_Name").SlicerItems(ActiveSheet.ListBox1.List(x)).Selected = False
    Next x
    
ElseIf ActiveSheet.ListBox1.Selected(1) = True And laAll1 = False Then
    laNone1 = False
    laAll1 = True
    ActiveSheet.ListBox1.Selected(0) = False
    ActiveWorkbook.SlicerCaches("Slicer_Company_Name").ClearManualFilter
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox1.Selected(x) = True
    Next x
    
Else
    laNone1 = False
    laAll1 = False
    ActiveSheet.ListBox1.Selected(0) = False
    ActiveSheet.ListBox1.Selected(1) = False
    For x = lCount To 2 Step -1
        If ActiveSheet.ListBox1.Selected(x) = True Then
            ActiveWorkbook.SlicerCaches("Slicer_Company_Name").SlicerItems(ActiveSheet.ListBox1.List(x)).Selected = True
        Else
            ActiveWorkbook.SlicerCaches("Slicer_Company_Name").SlicerItems(ActiveSheet.ListBox1.List(x)).Selected = False
        End If
    Next x
End If

Sheets("Sheet3").Range("B4:B100").ClearContents
Sheets("Data").Range("G2:G1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("B4")
Sheets("HR Dashboard").OLEObjects("ListBox2").ListFillRange = Sheets("Sheet3").Range("Department").Address(External:=True)

Sheets("Sheet3").Range("C4:C100").ClearContents
Sheets("Data").Range("H2:H1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("C4")
Sheets("HR Dashboard").OLEObjects("ListBox3").ListFillRange = Sheets("Sheet3").Range("Sub_Department").Address(External:=True)

Sheets("Sheet3").Range("D4:D100").ClearContents
Sheets("Data").Range("L2:L1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("D4")
Sheets("HR Dashboard").OLEObjects("ListBox4").ListFillRange = Sheets("Sheet3").Range("Location").Address(External:=True)

Sheets("Sheet3").Range("E4:E100").ClearContents
Sheets("Data").Range("M2:M1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("E4")
Sheets("HR Dashboard").OLEObjects("ListBox5").ListFillRange = Sheets("Sheet3").Range("Employee_Status").Address(External:=True)

Sheets("Sheet3").Range("F4:F100").ClearContents
Sheets("Data").Range("N2:N1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("F4")
Sheets("HR Dashboard").OLEObjects("ListBox6").ListFillRange = Sheets("Sheet3").Range("Employee_Category").Address(External:=True)

Sheets("Sheet3").Range("G4:G100").ClearContents
Sheets("Data").Range("O2:O1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("G4")
Sheets("HR Dashboard").OLEObjects("ListBox7").ListFillRange = Sheets("Sheet3").Range("Job_Category").Address(External:=True)

Sheets("Sheet3").Range("H4:H100").ClearContents
Sheets("Data").Range("AH2:AH1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("H4")
Sheets("HR Dashboard").OLEObjects("ListBox8").ListFillRange = Sheets("Sheet3").Range("Qualification_Levels").Address(External:=True)

Sheets("Sheet3").Range("I4:I100").ClearContents
Sheets("Data").Range("AA2:AA1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("I4")
Sheets("HR Dashboard").OLEObjects("ListBox9").ListFillRange = Sheets("Sheet3").Range("Religion").Address(External:=True)

Sheet2.pLoop = False

End Sub

Sub Listbox_2()
lCount = ActiveSheet.ListBox2.ListCount - 1

If ActiveSheet.ListBox2.Selected(0) = True And laNone2 = False Then
    laNone2 = True
    laAll2 = False
    ActiveSheet.ListBox2.Selected(1) = False
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox2.Selected(x) = False
        ActiveWorkbook.SlicerCaches("Slicer_Department").SlicerItems(ActiveSheet.ListBox2.List(x)).Selected = False
    Next x
    
ElseIf ActiveSheet.ListBox2.Selected(1) = True And laAll2 = False Then
    laNone2 = False
    laAll2 = True
    ActiveSheet.ListBox2.Selected(0) = False
    ActiveWorkbook.SlicerCaches("Slicer_Department").ClearManualFilter
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox2.Selected(x) = True
    Next x

Else
    laNone2 = False
    laAll2 = False
    ActiveSheet.ListBox2.Selected(0) = False
    ActiveSheet.ListBox2.Selected(1) = False
    For x = lCount To 2 Step -1
        If ActiveSheet.ListBox2.Selected(x) = True Then
            ActiveWorkbook.SlicerCaches("Slicer_Department").SlicerItems(ActiveSheet.ListBox2.List(x)).Selected = True
        Else
            ActiveWorkbook.SlicerCaches("Slicer_Department").SlicerItems(ActiveSheet.ListBox2.List(x)).Selected = False
        End If
    Next x
End If

Sheets("Sheet3").Range("A4:A100").ClearContents
Sheets("Data").Range("F2:F1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A4")
Sheets("HR Dashboard").OLEObjects("ListBox1").ListFillRange = Sheets("Sheet3").Range("Company").Address(External:=True)

Sheets("Sheet3").Range("C4:C100").ClearContents
Sheets("Data").Range("H2:H1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("C4")
Sheets("HR Dashboard").OLEObjects("ListBox3").ListFillRange = Sheets("Sheet3").Range("Sub_Department").Address(External:=True)

Sheets("Sheet3").Range("D4:D100").ClearContents
Sheets("Data").Range("L2:L1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("D4")
Sheets("HR Dashboard").OLEObjects("ListBox4").ListFillRange = Sheets("Sheet3").Range("Location").Address(External:=True)

Sheets("Sheet3").Range("E4:E100").ClearContents
Sheets("Data").Range("M2:M1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("E4")
Sheets("HR Dashboard").OLEObjects("ListBox5").ListFillRange = Sheets("Sheet3").Range("Employee_Status").Address(External:=True)

Sheets("Sheet3").Range("F4:F100").ClearContents
Sheets("Data").Range("N2:N1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("F4")
Sheets("HR Dashboard").OLEObjects("ListBox6").ListFillRange = Sheets("Sheet3").Range("Employee_Category").Address(External:=True)

Sheets("Sheet3").Range("G4:G100").ClearContents
Sheets("Data").Range("O2:O1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("G4")
Sheets("HR Dashboard").OLEObjects("ListBox7").ListFillRange = Sheets("Sheet3").Range("Job_Category").Address(External:=True)

Sheets("Sheet3").Range("H4:H100").ClearContents
Sheets("Data").Range("AH2:AH1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("H4")
Sheets("HR Dashboard").OLEObjects("ListBox8").ListFillRange = Sheets("Sheet3").Range("Qualification_Levels").Address(External:=True)

Sheets("Sheet3").Range("I4:I100").ClearContents
Sheets("Data").Range("AA2:AA1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("I4")
Sheets("HR Dashboard").OLEObjects("ListBox9").ListFillRange = Sheets("Sheet3").Range("Religion").Address(External:=True)

Sheet2.pLoop = False

End Sub

Sub Listbox_3()
lCount = ActiveSheet.ListBox3.ListCount - 1

If ActiveSheet.ListBox3.Selected(0) = True And laNone3 = False Then
    laNone3 = True
    laAll3 = False
    ActiveSheet.ListBox3.Selected(1) = False
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox3.Selected(x) = False
        ActiveWorkbook.SlicerCaches("Slicer_Sub_Department").SlicerItems(ActiveSheet.ListBox3.List(x)).Selected = False
    Next x
    
ElseIf ActiveSheet.ListBox3.Selected(1) = True And laAll3 = False Then
    laNone3 = False
    laAll3 = True
    ActiveSheet.ListBox3.Selected(0) = False
    ActiveWorkbook.SlicerCaches("Slicer_Sub_Department").ClearManualFilter
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox3.Selected(x) = True
    Next x
    
Else
    laNone3 = False
    laAll3 = False
    ActiveSheet.ListBox3.Selected(0) = False
    ActiveSheet.ListBox3.Selected(1) = False
    For x = lCount To 2 Step -1
        If ActiveSheet.ListBox3.Selected(x) = True Then
            ActiveWorkbook.SlicerCaches("Slicer_Sub_Department").SlicerItems(ActiveSheet.ListBox3.List(x)).Selected = True
        Else
            ActiveWorkbook.SlicerCaches("Slicer_Sub_Department").SlicerItems(ActiveSheet.ListBox3.List(x)).Selected = False
        End If
    Next x
End If

Sheets("Sheet3").Range("A4:A100").ClearContents
Sheets("Data").Range("F2:F1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A4")
Sheets("HR Dashboard").OLEObjects("ListBox1").ListFillRange = Sheets("Sheet3").Range("Company").Address(External:=True)

Sheets("Sheet3").Range("B4:B100").ClearContents
Sheets("Data").Range("G2:G1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("B4")
Sheets("HR Dashboard").OLEObjects("ListBox2").ListFillRange = Sheets("Sheet3").Range("Department").Address(External:=True)

Sheets("Sheet3").Range("D4:D100").ClearContents
Sheets("Data").Range("L2:L1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("D4")
Sheets("HR Dashboard").OLEObjects("ListBox4").ListFillRange = Sheets("Sheet3").Range("Location").Address(External:=True)

Sheets("Sheet3").Range("E4:E100").ClearContents
Sheets("Data").Range("M2:M1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("E4")
Sheets("HR Dashboard").OLEObjects("ListBox5").ListFillRange = Sheets("Sheet3").Range("Employee_Status").Address(External:=True)

Sheets("Sheet3").Range("F4:F100").ClearContents
Sheets("Data").Range("N2:N1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("F4")
Sheets("HR Dashboard").OLEObjects("ListBox6").ListFillRange = Sheets("Sheet3").Range("Employee_Category").Address(External:=True)

Sheets("Sheet3").Range("G4:G100").ClearContents
Sheets("Data").Range("O2:O1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("G4")
Sheets("HR Dashboard").OLEObjects("ListBox7").ListFillRange = Sheets("Sheet3").Range("Job_Category").Address(External:=True)

Sheets("Sheet3").Range("H4:H100").ClearContents
Sheets("Data").Range("AH2:AH1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("H4")
Sheets("HR Dashboard").OLEObjects("ListBox8").ListFillRange = Sheets("Sheet3").Range("Qualification_Levels").Address(External:=True)

Sheets("Sheet3").Range("I4:I100").ClearContents
Sheets("Data").Range("AA2:AA1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("I4")
Sheets("HR Dashboard").OLEObjects("ListBox9").ListFillRange = Sheets("Sheet3").Range("Religion").Address(External:=True)

Sheet2.pLoop = False

End Sub

Sub Listbox_4()
lCount = ActiveSheet.ListBox4.ListCount - 1

If ActiveSheet.ListBox4.Selected(0) = True And laNone4 = False Then
    laNone4 = True
    laAll4 = False
    ActiveSheet.ListBox4.Selected(1) = False
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox4.Selected(x) = False
        ActiveWorkbook.SlicerCaches("Slicer_Location").SlicerItems(ActiveSheet.ListBox4.List(x)).Selected = False
    Next x
    
ElseIf ActiveSheet.ListBox4.Selected(1) = True And laAll4 = False Then
    laNone4 = False
    laAll4 = True
    ActiveSheet.ListBox4.Selected(0) = False
    ActiveWorkbook.SlicerCaches("Slicer_Location").ClearManualFilter
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox4.Selected(x) = True
    Next x

Else
    laNone4 = False
    laAll4 = False
    ActiveSheet.ListBox4.Selected(0) = False
    ActiveSheet.ListBox4.Selected(1) = False
    For x = lCount To 2 Step -1
        If ActiveSheet.ListBox4.Selected(x) = True Then
            ActiveWorkbook.SlicerCaches("Slicer_Location").SlicerItems(ActiveSheet.ListBox4.List(x)).Selected = True
        Else
            ActiveWorkbook.SlicerCaches("Slicer_Location").SlicerItems(ActiveSheet.ListBox4.List(x)).Selected = False
        End If
    Next x
End If

Sheets("Sheet3").Range("A4:A100").ClearContents
Sheets("Data").Range("F2:F1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A4")
Sheets("HR Dashboard").OLEObjects("ListBox1").ListFillRange = Sheets("Sheet3").Range("Company").Address(External:=True)

Sheets("Sheet3").Range("B4:B100").ClearContents
Sheets("Data").Range("G2:G1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("B4")
Sheets("HR Dashboard").OLEObjects("ListBox2").ListFillRange = Sheets("Sheet3").Range("Department").Address(External:=True)

Sheets("Sheet3").Range("C4:C100").ClearContents
Sheets("Data").Range("H2:H1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("C4")
Sheets("HR Dashboard").OLEObjects("ListBox3").ListFillRange = Sheets("Sheet3").Range("Sub_Department").Address(External:=True)

Sheets("Sheet3").Range("E4:E100").ClearContents
Sheets("Data").Range("M2:M1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("E4")
Sheets("HR Dashboard").OLEObjects("ListBox5").ListFillRange = Sheets("Sheet3").Range("Employee_Status").Address(External:=True)

Sheets("Sheet3").Range("F4:F100").ClearContents
Sheets("Data").Range("N2:N1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("F4")
Sheets("HR Dashboard").OLEObjects("ListBox6").ListFillRange = Sheets("Sheet3").Range("Employee_Category").Address(External:=True)

Sheets("Sheet3").Range("G4:G100").ClearContents
Sheets("Data").Range("O2:O1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("G4")
Sheets("HR Dashboard").OLEObjects("ListBox7").ListFillRange = Sheets("Sheet3").Range("Job_Category").Address(External:=True)

Sheets("Sheet3").Range("H4:H100").ClearContents
Sheets("Data").Range("AH2:AH1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("H4")
Sheets("HR Dashboard").OLEObjects("ListBox8").ListFillRange = Sheets("Sheet3").Range("Qualification_Levels").Address(External:=True)

Sheets("Sheet3").Range("I4:I100").ClearContents
Sheets("Data").Range("AA2:AA1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("I4")
Sheets("HR Dashboard").OLEObjects("ListBox9").ListFillRange = Sheets("Sheet3").Range("Religion").Address(External:=True)

Sheet2.pLoop = False

End Sub

Sub Listbox_5()
lCount = ActiveSheet.ListBox5.ListCount - 1

If ActiveSheet.ListBox5.Selected(0) = True And laNone5 = False Then
    laNone5 = True
    laAll5 = False
    ActiveSheet.ListBox5.Selected(1) = False
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox5.Selected(x) = False
        ActiveWorkbook.SlicerCaches("Slicer_Employee_Status").SlicerItems(ActiveSheet.ListBox5.List(x)).Selected = False
    Next x
    
ElseIf ActiveSheet.ListBox5.Selected(1) = True And laAll5 = False Then
    laNone5 = False
    laAll5 = True
    ActiveSheet.ListBox5.Selected(0) = False
    ActiveWorkbook.SlicerCaches("Slicer_Employee_Status").ClearManualFilter
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox5.Selected(x) = True
    Next x

Else
    laNone5 = False
    laAll5 = False
    ActiveSheet.ListBox5.Selected(0) = False
    ActiveSheet.ListBox5.Selected(1) = False
    For x = lCount To 2 Step -1
        If ActiveSheet.ListBox5.Selected(x) = True Then
            ActiveWorkbook.SlicerCaches("Slicer_Employee_Status").SlicerItems(ActiveSheet.ListBox5.List(x)).Selected = True
        Else
            ActiveWorkbook.SlicerCaches("Slicer_Employee_Status").SlicerItems(ActiveSheet.ListBox5.List(x)).Selected = False
        End If
    Next x
End If

Sheets("Sheet3").Range("A4:A100").ClearContents
Sheets("Data").Range("F2:F1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A4")
Sheets("HR Dashboard").OLEObjects("ListBox1").ListFillRange = Sheets("Sheet3").Range("Company").Address(External:=True)

Sheets("Sheet3").Range("B4:B100").ClearContents
Sheets("Data").Range("G2:G1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("B4")
Sheets("HR Dashboard").OLEObjects("ListBox2").ListFillRange = Sheets("Sheet3").Range("Department").Address(External:=True)

Sheets("Sheet3").Range("C4:C100").ClearContents
Sheets("Data").Range("H2:H1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("C4")
Sheets("HR Dashboard").OLEObjects("ListBox3").ListFillRange = Sheets("Sheet3").Range("Sub_Department").Address(External:=True)

Sheets("Sheet3").Range("D4:D100").ClearContents
Sheets("Data").Range("L2:L1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("D4")
Sheets("HR Dashboard").OLEObjects("ListBox4").ListFillRange = Sheets("Sheet3").Range("Location").Address(External:=True)

Sheets("Sheet3").Range("F4:F100").ClearContents
Sheets("Data").Range("N2:N1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("F4")
Sheets("HR Dashboard").OLEObjects("ListBox6").ListFillRange = Sheets("Sheet3").Range("Employee_Category").Address(External:=True)

Sheets("Sheet3").Range("G4:G100").ClearContents
Sheets("Data").Range("O2:O1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("G4")
Sheets("HR Dashboard").OLEObjects("ListBox7").ListFillRange = Sheets("Sheet3").Range("Job_Category").Address(External:=True)

Sheets("Sheet3").Range("H4:H100").ClearContents
Sheets("Data").Range("AH2:AH1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("H4")
Sheets("HR Dashboard").OLEObjects("ListBox8").ListFillRange = Sheets("Sheet3").Range("Qualification_Levels").Address(External:=True)

Sheets("Sheet3").Range("I4:I100").ClearContents
Sheets("Data").Range("AA2:AA1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("I4")
Sheets("HR Dashboard").OLEObjects("ListBox9").ListFillRange = Sheets("Sheet3").Range("Religion").Address(External:=True)

Sheet2.pLoop = False

End Sub

Sub Listbox_6()
lCount = ActiveSheet.ListBox6.ListCount - 1

If ActiveSheet.ListBox6.Selected(0) = True And laNone6 = False Then
    laNone6 = True
    laAll6 = False
    ActiveSheet.ListBox6.Selected(1) = False
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox6.Selected(x) = False
        ActiveWorkbook.SlicerCaches("Slicer_Employee_Category").SlicerItems(ActiveSheet.ListBox6.List(x)).Selected = False
    Next x
    
ElseIf ActiveSheet.ListBox6.Selected(1) = True And laAll6 = False Then
    laNone6 = False
    laAll6 = True
    ActiveSheet.ListBox6.Selected(0) = False
    ActiveWorkbook.SlicerCaches("Slicer_Employee_Category").ClearManualFilter
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox6.Selected(x) = True
    Next x

Else
    laNone6 = False
    laAll6 = False
    ActiveSheet.ListBox6.Selected(0) = False
    ActiveSheet.ListBox6.Selected(1) = False
    For x = lCount To 2 Step -1
        If ActiveSheet.ListBox6.Selected(x) = True Then
            ActiveWorkbook.SlicerCaches("Slicer_Employee_Category").SlicerItems(ActiveSheet.ListBox6.List(x)).Selected = True
        Else
            ActiveWorkbook.SlicerCaches("Slicer_Employee_Category").SlicerItems(ActiveSheet.ListBox6.List(x)).Selected = False
        End If
    Next x
End If

Sheets("Sheet3").Range("A4:A100").ClearContents
Sheets("Data").Range("F2:F1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A4")
Sheets("HR Dashboard").OLEObjects("ListBox1").ListFillRange = Sheets("Sheet3").Range("Company").Address(External:=True)

Sheets("Sheet3").Range("B4:B100").ClearContents
Sheets("Data").Range("G2:G1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("B4")
Sheets("HR Dashboard").OLEObjects("ListBox2").ListFillRange = Sheets("Sheet3").Range("Department").Address(External:=True)

Sheets("Sheet3").Range("C4:C100").ClearContents
Sheets("Data").Range("H2:H1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("C4")
Sheets("HR Dashboard").OLEObjects("ListBox3").ListFillRange = Sheets("Sheet3").Range("Sub_Department").Address(External:=True)

Sheets("Sheet3").Range("D4:D100").ClearContents
Sheets("Data").Range("L2:L1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("D4")
Sheets("HR Dashboard").OLEObjects("ListBox4").ListFillRange = Sheets("Sheet3").Range("Location").Address(External:=True)

Sheets("Sheet3").Range("E4:E100").ClearContents
Sheets("Data").Range("M2:M1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("E4")
Sheets("HR Dashboard").OLEObjects("ListBox5").ListFillRange = Sheets("Sheet3").Range("Employee_Status").Address(External:=True)

Sheets("Sheet3").Range("G4:G100").ClearContents
Sheets("Data").Range("O2:O1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("G4")
Sheets("HR Dashboard").OLEObjects("ListBox7").ListFillRange = Sheets("Sheet3").Range("Job_Category").Address(External:=True)

Sheets("Sheet3").Range("H4:H100").ClearContents
Sheets("Data").Range("AH2:AH1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("H4")
Sheets("HR Dashboard").OLEObjects("ListBox8").ListFillRange = Sheets("Sheet3").Range("Qualification_Levels").Address(External:=True)

Sheets("Sheet3").Range("I4:I100").ClearContents
Sheets("Data").Range("AA2:AA1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("I4")
Sheets("HR Dashboard").OLEObjects("ListBox9").ListFillRange = Sheets("Sheet3").Range("Religion").Address(External:=True)

Sheet2.pLoop = False

End Sub

Sub Listbox_7()
lCount = ActiveSheet.ListBox7.ListCount - 1

If ActiveSheet.ListBox7.Selected(0) = True And laNone7 = False Then
    laNone7 = True
    laAll7 = False
    ActiveSheet.ListBox7.Selected(1) = False
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox7.Selected(x) = False
        ActiveWorkbook.SlicerCaches("Slicer_Job_Category").SlicerItems(ActiveSheet.ListBox7.List(x)).Selected = False
    Next x
    
ElseIf ActiveSheet.ListBox7.Selected(1) = True And laAll7 = False Then
    laNone7 = False
    laAll7 = True
    ActiveSheet.ListBox7.Selected(0) = False
    ActiveWorkbook.SlicerCaches("Slicer_Job_Category").ClearManualFilter
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox7.Selected(x) = True
    Next x

Else
    laNone7 = False
    laAll7 = False
    ActiveSheet.ListBox7.Selected(0) = False
    ActiveSheet.ListBox7.Selected(1) = False
    For x = lCount To 2 Step -1
        If ActiveSheet.ListBox7.Selected(x) = True Then
            ActiveWorkbook.SlicerCaches("Slicer_Job_Category").SlicerItems(ActiveSheet.ListBox7.List(x)).Selected = True
        Else
            ActiveWorkbook.SlicerCaches("Slicer_Job_Category").SlicerItems(ActiveSheet.ListBox7.List(x)).Selected = False
        End If
    Next x
End If

Sheets("Sheet3").Range("A4:A100").ClearContents
Sheets("Data").Range("F2:F1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A4")
Sheets("HR Dashboard").OLEObjects("ListBox1").ListFillRange = Sheets("Sheet3").Range("Company").Address(External:=True)

Sheets("Sheet3").Range("B4:B100").ClearContents
Sheets("Data").Range("G2:G1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("B4")
Sheets("HR Dashboard").OLEObjects("ListBox2").ListFillRange = Sheets("Sheet3").Range("Department").Address(External:=True)

Sheets("Sheet3").Range("C4:C100").ClearContents
Sheets("Data").Range("H2:H1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("C4")
Sheets("HR Dashboard").OLEObjects("ListBox3").ListFillRange = Sheets("Sheet3").Range("Sub_Department").Address(External:=True)

Sheets("Sheet3").Range("D4:D100").ClearContents
Sheets("Data").Range("L2:L1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("D4")
Sheets("HR Dashboard").OLEObjects("ListBox4").ListFillRange = Sheets("Sheet3").Range("Location").Address(External:=True)

Sheets("Sheet3").Range("E4:E100").ClearContents
Sheets("Data").Range("M2:M1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("E4")
Sheets("HR Dashboard").OLEObjects("ListBox5").ListFillRange = Sheets("Sheet3").Range("Employee_Status").Address(External:=True)

Sheets("Sheet3").Range("F4:F100").ClearContents
Sheets("Data").Range("N2:N1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("F4")
Sheets("HR Dashboard").OLEObjects("ListBox6").ListFillRange = Sheets("Sheet3").Range("Employee_Category").Address(External:=True)

Sheets("Sheet3").Range("H4:H100").ClearContents
Sheets("Data").Range("AH2:AH1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("H4")
Sheets("HR Dashboard").OLEObjects("ListBox8").ListFillRange = Sheets("Sheet3").Range("Qualification_Levels").Address(External:=True)

Sheets("Sheet3").Range("I4:I100").ClearContents
Sheets("Data").Range("AA2:AA1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("I4")
Sheets("HR Dashboard").OLEObjects("ListBox9").ListFillRange = Sheets("Sheet3").Range("Religion").Address(External:=True)

Sheet2.pLoop = False

End Sub

Sub Listbox_8()
lCount = ActiveSheet.ListBox8.ListCount - 1

If ActiveSheet.ListBox8.Selected(0) = True And laNone8 = False Then
    laNone8 = True
    laAll8 = False
    ActiveSheet.ListBox8.Selected(1) = False
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox8.Selected(x) = False
        ActiveWorkbook.SlicerCaches("Slicer_Qualification_Levels").SlicerItems(ActiveSheet.ListBox8.List(x)).Selected = False
    Next x
    
ElseIf ActiveSheet.ListBox8.Selected(1) = True And laAll8 = False Then
    laNone8 = False
    laAll8 = True
    ActiveSheet.ListBox8.Selected(0) = False
    ActiveWorkbook.SlicerCaches("Slicer_Qualification_Levels").ClearManualFilter
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox8.Selected(x) = True
    Next x

Else
    laNone8 = False
    laAll8 = False
    ActiveSheet.ListBox8.Selected(0) = False
    ActiveSheet.ListBox8.Selected(1) = False
    For x = lCount To 2 Step -1
        If ActiveSheet.ListBox8.Selected(x) = True Then
            ActiveWorkbook.SlicerCaches("Slicer_Qualification_Levels").SlicerItems(ActiveSheet.ListBox8.List(x)).Selected = True
        Else
            ActiveWorkbook.SlicerCaches("Slicer_Qualification_Levels").SlicerItems(ActiveSheet.ListBox8.List(x)).Selected = False
        End If
    Next x
End If

Sheets("Sheet3").Range("A4:A100").ClearContents
Sheets("Data").Range("F2:F1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A4")
Sheets("HR Dashboard").OLEObjects("ListBox1").ListFillRange = Sheets("Sheet3").Range("Company").Address(External:=True)

Sheets("Sheet3").Range("B4:B100").ClearContents
Sheets("Data").Range("G2:G1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("B4")
Sheets("HR Dashboard").OLEObjects("ListBox2").ListFillRange = Sheets("Sheet3").Range("Department").Address(External:=True)

Sheets("Sheet3").Range("C4:C100").ClearContents
Sheets("Data").Range("H2:H1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("C4")
Sheets("HR Dashboard").OLEObjects("ListBox3").ListFillRange = Sheets("Sheet3").Range("Sub_Department").Address(External:=True)

Sheets("Sheet3").Range("D4:D100").ClearContents
Sheets("Data").Range("L2:L1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("D4")
Sheets("HR Dashboard").OLEObjects("ListBox4").ListFillRange = Sheets("Sheet3").Range("Location").Address(External:=True)

Sheets("Sheet3").Range("E4:E100").ClearContents
Sheets("Data").Range("M2:M1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("E4")
Sheets("HR Dashboard").OLEObjects("ListBox5").ListFillRange = Sheets("Sheet3").Range("Employee_Status").Address(External:=True)

Sheets("Sheet3").Range("F4:F100").ClearContents
Sheets("Data").Range("N2:N1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("F4")
Sheets("HR Dashboard").OLEObjects("ListBox6").ListFillRange = Sheets("Sheet3").Range("Employee_Category").Address(External:=True)

Sheets("Sheet3").Range("G4:G100").ClearContents
Sheets("Data").Range("O2:O1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("G4")
Sheets("HR Dashboard").OLEObjects("ListBox7").ListFillRange = Sheets("Sheet3").Range("Job_Category").Address(External:=True)

Sheets("Sheet3").Range("I4:I100").ClearContents
Sheets("Data").Range("AA2:AA1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("I4")
Sheets("HR Dashboard").OLEObjects("ListBox9").ListFillRange = Sheets("Sheet3").Range("Religion").Address(External:=True)

Sheet2.pLoop = False

End Sub

Sub Listbox_9()
lCount = ActiveSheet.ListBox9.ListCount - 1

If ActiveSheet.ListBox9.Selected(0) = True And laNone9 = False Then
    laNone9 = True
    laAll9 = False
    ActiveSheet.ListBox9.Selected(1) = False
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox9.Selected(x) = False
        ActiveWorkbook.SlicerCaches("Slicer_Religion").SlicerItems(ActiveSheet.ListBox9.List(x)).Selected = False
    Next x
    
ElseIf ActiveSheet.ListBox9.Selected(1) = True And laAll9 = False Then
    laNone9 = False
    laAll9 = True
    ActiveSheet.ListBox9.Selected(0) = False
    ActiveWorkbook.SlicerCaches("Slicer_Religion").ClearManualFilter
    For x = lCount To 2 Step -1
        ActiveSheet.ListBox9.Selected(x) = True
    Next x

Else
    laNone9 = False
    laAll9 = False
    ActiveSheet.ListBox9.Selected(0) = False
    ActiveSheet.ListBox9.Selected(1) = False
    For x = lCount To 2 Step -1
        If ActiveSheet.ListBox9.Selected(x) = True Then
            ActiveWorkbook.SlicerCaches("Slicer_Religion").SlicerItems(ActiveSheet.ListBox9.List(x)).Selected = True
        Else
            ActiveWorkbook.SlicerCaches("Slicer_Religion").SlicerItems(ActiveSheet.ListBox9.List(x)).Selected = False
        End If
    Next x
End If

Sheets("Sheet3").Range("A4:A100").ClearContents
Sheets("Data").Range("F2:F1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A4")
Sheets("HR Dashboard").OLEObjects("ListBox1").ListFillRange = Sheets("Sheet3").Range("Company").Address(External:=True)

Sheets("Sheet3").Range("B4:B100").ClearContents
Sheets("Data").Range("G2:G1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("B4")
Sheets("HR Dashboard").OLEObjects("ListBox2").ListFillRange = Sheets("Sheet3").Range("Department").Address(External:=True)

Sheets("Sheet3").Range("C4:C100").ClearContents
Sheets("Data").Range("H2:H1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("C4")
Sheets("HR Dashboard").OLEObjects("ListBox3").ListFillRange = Sheets("Sheet3").Range("Sub_Department").Address(External:=True)

Sheets("Sheet3").Range("D4:D100").ClearContents
Sheets("Data").Range("L2:L1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("D4")
Sheets("HR Dashboard").OLEObjects("ListBox4").ListFillRange = Sheets("Sheet3").Range("Location").Address(External:=True)

Sheets("Sheet3").Range("E4:E100").ClearContents
Sheets("Data").Range("M2:M1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("E4")
Sheets("HR Dashboard").OLEObjects("ListBox5").ListFillRange = Sheets("Sheet3").Range("Employee_Status").Address(External:=True)

Sheets("Sheet3").Range("F4:F100").ClearContents
Sheets("Data").Range("N2:N1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("F4")
Sheets("HR Dashboard").OLEObjects("ListBox6").ListFillRange = Sheets("Sheet3").Range("Employee_Category").Address(External:=True)

Sheets("Sheet3").Range("G4:G100").ClearContents
Sheets("Data").Range("O2:O1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("G4")
Sheets("HR Dashboard").OLEObjects("ListBox7").ListFillRange = Sheets("Sheet3").Range("Job_Category").Address(External:=True)

Sheets("Sheet3").Range("H4:H100").ClearContents
Sheets("Data").Range("AH2:AH1000").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("H4")
Sheets("HR Dashboard").OLEObjects("ListBox8").ListFillRange = Sheets("Sheet3").Range("Qualification_Levels").Address(External:=True)

Sheet2.pLoop = False

End Sub


TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383 -
Here is a link to the workbook I used:
https://we.tl/t-QUyumjcb8m
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1572 -
Good luck Trowad!
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383 -
Thanks Mark! I'm actually going to need it this time. Quite the challenge!
Respond to TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
0
Thank you
To get all unique values back in the listboxes is quite cumbersome (and can't be done in specific circumstances). Here are some alterations:

Replace the code for Sheet3 with:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow, lRow2 As Integer
Application.EnableEvents = False

If Not Intersect(Target, Columns("A")) Is Nothing Then
    Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    lRow2 = Range("K" & Rows.Count).End(xlUp).Row
    Range("A2:A" & lRow).Name = "Company"
    If lRow > lRow2 Then
        Columns("A").Copy Columns("K")
        Range("K2:K" & lRow).Name = "Company2"
    End If
ElseIf Not Intersect(Target, Columns("B")) Is Nothing Then
    Columns("B").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    lRow2 = Range("L" & Rows.Count).End(xlUp).Row
    Range("B2:B" & lRow).Name = "Department"
    If lRow > lRow2 Then
        Columns("B").Copy Columns("L")
        Range("L2:L" & lRow).Name = "Department2"
    End If
ElseIf Not Intersect(Target, Columns("C")) Is Nothing Then
    Columns("C").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    lRow2 = Range("M" & Rows.Count).End(xlUp).Row
    Range("C2:C" & lRow).Name = "Sub_Department"
    If lRow > lRow2 Then
        Columns("C").Copy Columns("M")
        Range("M2:M" & lRow).Name = "Sub_Department2"
    End If
ElseIf Not Intersect(Target, Columns("D")) Is Nothing Then
    Columns("D").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Range("D" & Rows.Count).End(xlUp).Row
    lRow2 = Range("N" & Rows.Count).End(xlUp).Row
    Range("D2:D" & lRow).Name = "Location"
    If lRow > lRow2 Then
        Columns("D").Copy Columns("N")
        Range("N2:N" & lRow).Name = "Location2"
    End If
ElseIf Not Intersect(Target, Columns("E")) Is Nothing Then
    Columns("E").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Range("E" & Rows.Count).End(xlUp).Row
    lRow2 = Range("O" & Rows.Count).End(xlUp).Row
    Range("E2:E" & lRow).Name = "Employee_Status"
    If lRow > lRow2 Then
        Columns("E").Copy Columns("O")
        Range("O2:O" & lRow).Name = "Employee_Status2"
    End If
ElseIf Not Intersect(Target, Columns("F")) Is Nothing Then
    Columns("F").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Range("F" & Rows.Count).End(xlUp).Row
    lRow2 = Range("P" & Rows.Count).End(xlUp).Row
    Range("F2:F" & lRow).Name = "Employee_Category"
    If lRow > lRow2 Then
        Columns("F").Copy Columns("P")
        Range("P2:P" & lRow).Name = "Employee_Category2"
    End If
ElseIf Not Intersect(Target, Columns("G")) Is Nothing Then
    Columns("G").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Range("G" & Rows.Count).End(xlUp).Row
    lRow2 = Range("Q" & Rows.Count).End(xlUp).Row
    Range("G2:G" & lRow).Name = "Job_Category"
    If lRow > lRow2 Then
        Columns("G").Copy Columns("Q")
        Range("Q2:Q" & lRow).Name = "Job_Category2"
    End If
ElseIf Not Intersect(Target, Columns("H")) Is Nothing Then
    Columns("H").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Range("H" & Rows.Count).End(xlUp).Row
    lRow2 = Range("R" & Rows.Count).End(xlUp).Row
    Range("H2:H" & lRow).Name = "Qualification_levels"
    If lRow > lRow2 Then
        Columns("H").Copy Columns("R")
        Range("R2:R" & lRow).Name = "Qualification_levels2"
    End If
ElseIf Not Intersect(Target, Columns("I")) Is Nothing Then
    Columns("I").RemoveDuplicates Columns:=1, Header:=xlYes
    lRow = Range("I" & Rows.Count).End(xlUp).Row
    lRow2 = Range("S" & Rows.Count).End(xlUp).Row
    Range("I2:I" & lRow).Name = "Religion"
    If lRow > lRow2 Then
        Columns("I").Copy Columns("S")
        Range("S2:S" & lRow).Name = "Religion2"
    End If
Else
    Exit Sub
End If

Cells.Interior.Pattern = xlNone

Application.EnableEvents = True
End Sub

This will update a second range of named ranges which will always hold the highest number of unique values to re-populate the listboxes when using the code below:

Additional code for Module (the same one or create another):
Sub ResetListboxes()
Sheets("HR Dashboard").OLEObjects("ListBox1").ListFillRange = Sheets("Sheet3").Range("Company2").Address(External:=True)
Sheets("HR Dashboard").OLEObjects("ListBox2").ListFillRange = Sheets("Sheet3").Range("Department2").Address(External:=True)
Sheets("HR Dashboard").OLEObjects("ListBox3").ListFillRange = Sheets("Sheet3").Range("Sub_Department2").Address(External:=True)
Sheets("HR Dashboard").OLEObjects("ListBox4").ListFillRange = Sheets("Sheet3").Range("Location2").Address(External:=True)
Sheets("HR Dashboard").OLEObjects("ListBox5").ListFillRange = Sheets("Sheet3").Range("Employee_Status2").Address(External:=True)
Sheets("HR Dashboard").OLEObjects("ListBox6").ListFillRange = Sheets("Sheet3").Range("Employee_Category2").Address(External:=True)
Sheets("HR Dashboard").OLEObjects("ListBox7").ListFillRange = Sheets("Sheet3").Range("Job_Category2").Address(External:=True)
Sheets("HR Dashboard").OLEObjects("ListBox8").ListFillRange = Sheets("Sheet3").Range("Qualification_Levels2").Address(External:=True)
Sheets("HR Dashboard").OLEObjects("ListBox9").ListFillRange = Sheets("Sheet3").Range("Religion2").Address(External:=True)
End Sub

For easy acces create a button to run this code.

But first check All in 2 different listboxes to create the second list of named ranges.

Best regards,
Trowa
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383 -
Here is a link to the workbook where everything is setup:
https://we.tl/t-OxIMxN23QM
Respond to TrowaD