Populate Combo Box Based on Table Field

[BrittKnee]

Hi. I am trying to populate a combo box based on a field in a table. I am using the below code, but it is not populating. Any help is appreciated.

Private Sub DeptCombo_Change()

Dim ws, wsht As Worksheet
Dim rng As Range

'Populate Combo Box

  Set ws = Sheets("Readiness_All")
  Set wsht = Sheets("Dept_Selection")
  Set rng = Range("Readiness[Department]")

wsht.DeptCombo.List = ws.rng.Value

End Sub

 

[BrittKnee]

I've decided to nix the combo box and would like to filter the table based on the selection in a drop-down list. I am using the below code, but it's not working. TIA!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("Filtervalue").Address Then
Range("Readiness").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Readiness[Department")
End If
End Sub

 

[BrittKnee]

I've decided to nix the combo box and would like to filter the table based on the selection in a drop-down list. I am using the below code, but it's not working. TIA!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("Filtervalue").Address Then
Range("Readiness").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Readiness[Department")
End If
End Sub

This works:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


Dim ws As Worksheet

    Set ws = Sheets("Readiness_All")

    With Me
        If Not Intersect(Target, .Range("FilterValue")) Is Nothing Then
          If Target.Value <> "" Then
                .AutoFilterMode = False
                .Range("Readiness").AutoFilter Field:=7, Criteria1:=Target.Value
        End If
      End If
        
    End With


End Sub