Sheet Change when Value is Selected

Hello,

I have a data validation list / drop down menu of dates in mmmm d format. I am trying to code the workbook so that when a date is selected, you are taken to the sheet with the matching date name. For example, if I select June 29, I want to automatically be taken to the sheet labeled June 29.

This is the code I currently have:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not (Application.Intersect(Range("B1"), Target) Is Nothing) Then _
        This Workbook.Sheets(Target.Value).Activate
End Sub

B1 is the drop down list selection. When I debug, the first line errors. What am I doing wrong?

DEMO.xlsm

When asking for help with a VBA error, always give us the error number and error description.

The error must not be on the first line, because there's nothing wrong with the first line.

ThisWorkbook has to be all one word. That will give you a compile error.

Hi goldyf_ishy,

The thing is.. the drop down actually contains a date made to look like just month day (target.value is 2023-07-06 for july 6).

Try:

If Not (Application.Intersect(Range("B1"), Target) Is Nothing) Then _
        ThisWorkbook.Sheets(Format(Target.Value, "mmmm d")).Activate

Glad to hear it
Please don't forget to mark the thread as Solved if you are satisfied? Thanks!