Drop Down Menu Help!

[digbeefeebee]

Hello,

I am sure this is a quick fix but for the life me I can't work it out!
I am creating a planner with all months going along Row 1.
I want to create a drop down so when I select e.g. March it will take me to the month March on my spreadsheet.

It is going to be a rather long spreadsheet so was looking for a nicer look to navigate around rather than hyperlinking.

Could anyone help me?

Thank you so much!

Jodie

 

[Yongle]

Selecting March from dropdown in A1 takes user to D1

Place this code in the SHEET module
RightClick on sheet tab \ select ViewCode \ paste code into VBA editor \ back to Excel with {ALT}{F11}
Save workbook as macro-enabled

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A1" Then Application.Goto (Rows(1).Find(Target))
End Sub

With worksheet looking like this

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​	March	January	February	March	April

Sheet: Sheet2

 

[digbeefeebee]

Thank you, this worked however, I am noticing when I do this it removed my data validation after I select e.g. March from the drop down? Any Thoughts on this.

 

[Jaafar Tribak]

Thank you, this worked however, I am noticing when I do this it removed my data validation after I select e.g. March from the drop down? Any Thoughts on this.

There is nothing in that code that would remove the data validation...Do you have any other code that might be removing the DV ?

 

[digbeefeebee]

No, I am using no further code - I don't work with code a lot so very rarely use it.

When I use it once it disappears and I can't make a further selection, but I can type the cell the month and it will take me to the cell containing it. Just removes the 'drop down'. However, when I close the spreadsheet down and reopen it will appear again.

 

[Yongle]

As @Jaafar Tribak says there is nothing in the code to change data validation in cell A1 in any way

Similar code for you to try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A1" Then Rows(1).Find(Target).Activate
End Sub

If you are still having problems try a creating new workbook and test to see if it also happens there