Excel jump to a specific column/cell based on a dropdown menu

Question

I'm volunteering in a nursing home and creating easy solutions for the carers better help their residents. I'm working on an an interactive Excel calendar but I need a functionality I've been searching the Internet for, without any success.

I need a solution for my excel (VBA, I'm guessing based on similar questions) that allows the user to select a calendar date from a dropdown list with lots of dates and then the user will jump to the specific cell with that date on another range.

My range with dates is in a single row: F9:UA9 and my dropdown is in B11.

I'd like that by selecting "20/09/2020" on the dropdown, the user would move to the cell in range F9:UA9 where that date first appears.

Here's some sample data. [1]: https://i.sstatic.net/l4GjH.png

Thanks in advance!

Answer 1

The question you linked to has the basic shell of what you need. You can use Application.Match to find the first instance of the date, and then Application.Goto to scroll to it.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dropDownRange As Range
    Set dropDownRange = Me.Range("B11")

    '// Check if the change is happening in your dropdown cell
    If Not Intersect(Target, dropDownRange) Is Nothing Then
        Dim lookInRange As Range
        Set lookInRange = Me.Range("F9:UA9")

        Dim matchResult As Variant
        matchResult = Application.Match(CLng(dropDownRange.Value), lookInRange, 0)

        '// First check that the corresponding date was found
        If Not IsError(matchResult) Then
            Application.Goto lookInRange.Cells(matchResult), Scroll:=True
        End If
    End If
End Sub

Add this code in the sheet code module:

If you want to scroll to row 1, perhaps the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dropDownRange As Range
    Set dropDownRange = Me.Range("B11")

    '// Check if the change is happening in your dropdown cell
    If Not Intersect(Target, dropDownRange) Is Nothing Then
        Dim lookInRange As Range
        Set lookInRange = Me.Range("F9:UA9")

        Dim matchResult As Variant
        matchResult = Application.Match(CLng(dropDownRange.Value), lookInRange, 0)

        '// First check that the corresponding date was found
        If Not IsError(matchResult) Then
            Dim matchRange As Range
            Set matchRange = lookInRange.Cells(matchResult)

            Application.Goto  Me.Cells(1, matchRange.Column), Scroll:=True
        End If
    End If
End Sub