VBA to control filters by manual entry

Hi All,

I'm working on a spread sheet that only shows data entries in a table based on data entered in by the user. I created an example using airline arrival and departure times.

What I want to do is have the user select the Airline they want information on from a drop down list of all the airlines. That turns on a filter which shows only the data for the airline selected. I got this working already as you will see in the sample sheet.

The next part is more tricky. There are 3 other cells below the airline selection cell. The first is threshold. I would like this to control column F which is the detention time of the aircraft. So if it is set to 0:30:00 it will filter out any flights that were not in detention for less than 30 minutes.

The other two cells set the date range for the data. Cell B3 should filter out any dates before (less than) its value, and allow everything greater than (after) its value in the arrival column.

Cell B4 is the opposite as it is the departure date and time. It should filter out anything from the departure column that is after (greater than) and allow anything that is before (less than) it in the filter for that column.


Any help is much appreciated!

Thanks,

Neo

Perhaps something along the lines of:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngInterest As Range, xlCalc As XlCalculation
    On Error GoTo ExitPoint
    Set rngInterest = Intersect(Range("B1:B4"), Target)
    If Not rngInterest Is Nothing Then
        With Application
            xlCalc = .Calculation
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
            With Sheet1.Range("A7", "G77")
                .AutoFilter
                .AutoFilter 1, Range("B1").Value
                .AutoFilter 6, "<" & CDbl(Range("B2").Value)
                .AutoFilter 4, ">=" & CDbl(Range("B3").Value)
                .AutoFilter 5, "<=" & CDbl(Range("B4").Value)
            End With
ExitPoint:
            Set rngInterest = Nothing
            .ScreenUpdating = True
            .Calculation = xlCalc
            .EnableEvents = True
        End With
    End If
End Sub

Marcol, per the earlier code IMO it is always worth toggling Events & Calculation whenever modifying AutoFilters as altering row visibility is a Volatile action.
(ie with each filter operation events/calculations would otherwise be invoked [as deemed nec. by XL - rather than calculating after all had been adjusted])

Probably worth adding that it was my belief that any manual entry within range B1:B4 was to affect the filters - not just B1 - hence the additional differences from the above.

Try this

I have added dymamic listings in a new sheet, popup calendars and input forms.

DonkeyOte
Took on board what you said, please have a look if you have time.

New file appended

[EDIT]

corrected lines to

.AutoFilter 5, "<=" & (CDbl(Range("B4")) + 0.9999)

To include all of the departure day

Also I think

.AutoFilter 6, "<" & CDbl(Range("B2").Value)

should be

.AutoFilter 6, ">" & CDbl(Range("B2").Value)
'or just
.AutoFilter 6, ">" & Range("B2")

and have used the latter in the code.
Just change the operator in two lines in the code, if I am wrong

Cheers
Alistair