Calling a function on dropdown change

Question

I have a set of data that is dynamically created from a function in A2. I have column E that the user can place an x in that will be used to determine whether that employee should be copied to another table. In column I there is the cell I2 where the date can be changed to find the employees available after a set date and a number of dropdowns below that will filter the data, for example, setting the employee to Richard will just show his availability.

If anything adjusts the data in A to D such that the rows change then I need to fix up the x's the user has placed in column E to coincide with the filtered data, so if an x is placed next to Richard in row 10, if the user filters by Richard, they will now be in row 2 and the other rows have no data. To do this I have the following function:

Private Sub UpdateAllXValues()
    Dim row As Long, lastRow As Long
    Dim Sheet As Worksheet
    Set Sheet = ActiveSheet
    Dim rRange As Range
    
    ' Firstly clear all x values in the column
    Range("E2:E9999").ClearContents
    
    ' Loop through all visible rows and set x value based on the value in the SelectedEmployeeDictionary
    If Not SelectedEmployeeDictionary Is Nothing Then
        With Sheet
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
            For row = 2 To lastRow
                Set rRange = .Rows.Range("A" & row)
                If Not rRange Is Nothing Then
                    If Not IsTextEmpty(rRange.Value) Then
                        ' Place x or nothing in the corresponding row cell in column E
                        Dim rRangeToSet As Range
                        Set rRangeToSet = .Rows.Range("E" & row)
                            
                        If SelectedEmployeeDictionary.Exists(rRange.Value) Then
                            If SelectedEmployeeDictionary.Item(rRange.Value) = 0 Then
                                rRangeToSet.Value = ""
                            Else
                                rRangeToSet.Value = "X"
                            End If
                        End If
                    End If
                End If
            Next row
        End With
    End If
End Sub

I thought if I placed a call to the above function in Worksheet_Change it'd work but it's creating a cyclical issue where it's re-calling Worksheet_Change as it loops through the rows and adjusts the value in each cell in column E. The code works fine if I assign it to a button for testing but I'd obviously like it to automatically work if any of the data in I2, I5, I8, I11, I14 to filter the data in A to D. Since I5, I8, I11 and I14 I can't use the Worksheet_SelectionChange either as it doesn't trigger on a dropdown change. I however can use it for the date change in I2. Are there any ways I can trigger a function on a dropdown change?

Calling a function on dropdown change

0 Answers