CODEWITHSUNDEEP
vbaexcelexcel-2013
Michael Downey
Michael Downey

Reputation: 687

Excel Dynamic Dropdown go to Cell

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Range("B1").Value
        Case Is = "G&A Total"
        Application.Goto Range("G_A_Total"), Scroll:=True

        Case Is = "Accounting"
        Application.Goto Range("Accounting"), Scroll:=True

        Case Is = "Environmental"
        Application.Goto Range("Environmental"), Scroll:=True

        Case Is = "GSC"
        Application.Goto Range("GSC"), Scroll:=True

        Case Is = "Human Resources"
        Application.Goto Range("Human_Resources"), Scroll:=True

    End Select

End Sub

I currently have this code which works with a data verification drop down. What I want to know is there anyway to make this dynamic so that anything I put in the list becomes what it is pulling from instead of having to manually enter each one.

Current Edits below.

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Range("B1").Value
        Case Is = Range("A1")
        Application.Goto Range("G_A_Total"), Scroll:=True

        Case Is = Range("A2")
        Application.Goto Range("Accounting"), Scroll:=True

        Case Is = Range("A3")
        Application.Goto Range("Environmental"), Scroll:=True

        Case Is = Range("A4")
        Application.Goto Range("GSC"), Scroll:=True

        Case Is = Range("A5")
        Application.Goto Range("Human_Resources"), Scroll:=True
    End Select

End Sub

I changed out the specific Case Is to be cell references which reference the cells selected by Data Validation instead of refering to each individual cell is there a way to range name them then refer to the range name?

Maybe something such as this

    Select Case Range("B1").Value
            Case Is = Range("Departments")
            Application.Goto Range("DepartmentsGoTo"), Scroll:=True                
        End Select

Then have 2 ranged named column that I can update to avoid having to change the code often?

Upvotes: 1

Views: 2233

Answers (1)

Ravi Yenugu
Ravi Yenugu

Reputation: 3898

Basically, compare each Named range with the value in B1 

Private Sub Worksheet_Change(ByVal Target As Range)

For Each myName In ActiveWorkbook.Names    
    Select Case Range("B1").Value
      Case myName.Name
        Application.Goto Reference:=myName.Name, Scroll:=True
     End Select
Next

End Sub

Upvotes: 1

Related Questions