EXCEL 2013 VBA - Pragmatically add "(All)" to combo boxes to always be an option

[ectiger86]

I need to add "(All)" to always be an option for my 4 ActiveX dependent Combo Boxes. The Combo Boxes are populated by their Dynamic Named Ranges via VBA. Currently I have manually inserted (All) to be an option, but once the 1st combo boxes filter down (All) is no longer an option for the other combo's.

Any help would be greatly appreciated.

Here is my Fill code, It is pieced together from the internet.

Sub FillCombos()

    Dim CHART As Worksheet
    Dim DATA As Worksheet
    Set ch = ThisWorkbook.Sheets("CHART")
    Set da = ThisWorkbook.Sheets("DATA")
    
' Last Row
daLR = da.Cells(Rows.Count, 1).End(xlUp).Row
FirstTime = True

' Loop thru rows
For x = 2 To daLR
    If da.Cells(x, 1) <> "" And (InStr(blah, "|" & da.Cells(x, 1) & "|") = 0) Then
        If FirstTime = True Then
            FirstTime = False
         blah = "|" & blah & da.Cells(x, 1) & "|"
     Else
        blah = blah & da.Cells(x, 1) & "|"
    End If
End If
    

Next x

myArray = Split(blah, "|")

Sheet7.cmbRent.Clear

 For Each cell In myArray
    If cell <> "" Then
        Sheet7.cmbRent.AddItem (cell)
    End If
Next cell
      ThisWorkbook.Sheets("CHART").cmbRent.ListIndex = 0
   
   
   End Sub

And here is the code that changes my combos.

Option Explicit
Sub changeFilters()

    
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim wsChart As Worksheet
    Dim wsPivot As Worksheet
    Dim selCat  As Variant
    Dim selSub  As Variant
    Dim selLoc  As Variant
    Dim selCust As Variant
              
    
    Set wsChart = ThisWorkbook.Sheets("CHART")
    Set wsPivot = ThisWorkbook.Sheets("Pivot")
    Set pt = ThisWorkbook.Sheets("Pivot").PivotTables("PT1")
    Set selCat = ThisWorkbook.Sheets("CHART").Range("selCat")
    Set selSub = ThisWorkbook.Sheets("CHART").Range("selSub")
    Set selLoc = ThisWorkbook.Sheets("CHART").Range("selLoc")
    Set selCust = ThisWorkbook.Sheets("CHART").Range("selCust")
       
    
Application.ScreenUpdating = False
pt.ManualUpdate = True

ThisWorkbook.Sheets("Pivot").PivotTables("PT1").ClearAllFilters



For Each pi In pt.PivotFields("CATEGORY").PivotItems

    Select Case pi.Name
        Case [selCat]
            pi.Visible = True
        Case Else
            pi.Visible = False
        End Select
    Next pi


'Removes pivot items from pivot table except those cases defined below (by looping through)
For Each pi In pt.PivotFields("SUB-CATEGORY").PivotItems

    Select Case pi.Name
        Case [selSub]
            pi.Visible = True
        Case Else
            pi.Visible = False
        End Select
    Next pi



For Each pi In pt.PivotFields("LOCATION").PivotItems
    Select Case pi.Name
        Case [selLoc]
            pi.Visible = True
        Case Else
            pi.Visible = False
        End Select
    Next pi



For Each pi In pt.PivotFields("CUSTOMER").PivotItems
    Select Case pi.Name
        Case [selCust]
            pi.Visible = True
        Case Else
            pi.Visible = False
        End Select
    Next pi



'turn on automatic update / calculation in the Pivot Table

pt.ManualUpdate = False
Application.ScreenUpdating = True




End Sub