Navigating to cells as per the Drop Down

[nbkjytr]

Hello Friends,

Need help with excel. Working on tracker with has too many information to scroll from left to right.

Hence looking for a method where I can directly go to specific cell based upon the value in drop down (Data Validation) without any double click or select and click. I think VBA would work smoothly.

Example if I select June in drop down it should take me to the cell where June data (K12) is available.

Note: All the Data is available in the same Sheet. It only needs to navigate to the specific cell in the same sheet. Only the source of Data Validation is in a different Sheet in the same excel file.

Attaching a sample screenshot for both the sheets for your reference.

 

[Micron]

I think a combobox would be better than a validation list because the combo can have 2 columns whereas the data validation list cannot. So the 2nd combo column would have the column value that begins at the left of the desired column. After you make a selection, go to the value of the 2nd column of the combo.

 

[jolivanes]

Would a worksheet change with target as A2:A13 not work?
And before we continue, are the values in Row1 (or whichever Row has the month names) merged and Centered. If so, change them to Center Across Selection.
This will make your life so much easier.

 

[jolivanes]

With your Months names in A2:A13.
Goes in the sheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("A2:A13")
If Not Intersect(Target, rng) Is Nothing Then
    Rows(1).Cells(1, (Rows(1).Find(ActiveCell.Value).Column)).Select
End If
End Sub

 

[Bosquedeguate]

Here's a Worksheet_Change() event version

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
  With Target
    If .Row() = 1 And .Column() = 1 Then
      Set rng = Cells(11, WorksheetFunction.Match(Target, Range("$N$1:$N$12"), 0))
      rng.Select
      rng.Activate
    End If
  End With
End Sub

Book2 (Autosaved).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	DEC
2
3
4
5
6
7
8
9
10	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
11
Sheet6

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=$N$1:$N$12

 

[jolivanes]

When you are way out in the boonies, like "December", and you want to go back, you have the same problem that you want to solve with the macro.
Following will solve that problem.
Change references where required.
In the Sheet with all the data Module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A13")) Is Nothing Then
    Rows(1).Cells(1, (Rows(1).Find(ActiveCell.Value).Column)).Offset(1, 3).Activate
    Add_Button
End If
End Sub

In a regular Module (here Module1)

Sub Add_Button()
  Dim btn As Object
    Set btn = ActiveSheet.Buttons.Add(ActiveCell.Left - 20, ActiveCell.Top, ActiveCell.Width + 40, ActiveCell.Height)
    With btn
      .Name = "Home_Button"
      .OnAction = "Module1.Go_Home"    '<---- Change Module1 to whichever module the macro "Go_Home" code is in
      .Caption = "Home"
    End With
Application.CutCopyMode = False
End Sub

Sub Go_Home()
With ActiveSheet
.Shapes("Home_Button").Delete
.Cells(2, 2).Select
End With
End Sub

 

[nbkjytr]

With your Months names in A2:A13.
Goes in the sheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("A2:A13")
If Not Intersect(Target, rng) Is Nothing Then
    Rows(1).Cells(1, (Rows(1).Find(ActiveCell.Value).Column)).Select
End If
End Sub

Thank you for the response.

Just to add that
1) The source for Data Validation is in a different sheet ( Helper Sheet 1) in the same excel
2) And I want to execute this on the different sheet (Tracker - Sheet 2) in the same excel
a) On Tracker Sheet - 2 Data Validation Drop Down will be there and on the same sheet all the months along with date will be listed from left to right.

 

[jolivanes]

Please don't quote. All clutter we don't need.
In this case you could have said something like "Regarding your code from Post #4" or whichever Post number.

Re: "will be there" Where is there? I have assumed Range("A2"). Change as required.
Re: "will be listed from left to right" In which Row? I have assumed Row 1. Change as required.
Code goes into the Sheet Module where the dropdown is in Column A and the months are in Row 1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then
        Application.Goto Cells(1, Application.Match(Target, Rows(1), 0)).Offset(1, 3)
    End If
End Sub

 

[Micron]

I'm more in tune with Access which is probably why I just can't see how a validation list is the way to go. With a combo all should you need is one event and maybe 3 or 4 lines of code.

 

[nbkjytr]

I'm more in tune with Access which is probably why I just can't see how a validation list is the way to go. With a combo all should you need is one event and maybe 3 or 4 lines of code.

Please don't quote. All clutter we don't need.
In this case you could have said something like "Regarding your code from Post #4" or whichever Post number.

Re: "will be there" Where is there? I have assumed Range("A2"). Change as required.
Re: "will be listed from left to right" In which Row? I have assumed Row 1. Change as required.
Code goes into the Sheet Module where the dropdown is in Column A and the months are in Row 1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then
        Application.Goto Cells(1, Application.Match(Target, Rows(1), 0)).Offset(1, 3)
    End If
End Sub

Reply for Post # 8

Thank you once again for the response.
After inputting the code am getting the Compile error - “Sub or Function not defined”.