Button to change sheet and select a value from list

[M.Bachrach]

Hi everybody!
I would like to create buttons for the index page of my project that would bring the reader to a certain sheet and that automatically selects from a drop down list (data validation) depending on the country of interest.

The charts in the sheet are created retrieving data from databset with Vlookup's and change depending on country selected.

I know how to do the first step, but was not able to find a solution to the second one (code for the drop down menu).

Anybody can help?I cannot attach the file because data is confidential but the drop down list cell is D5 in the sheet "OpCo" (the one that needs to be activated).

thank you!

 

[cb12]

You could try

Sub change_sheet()
    Dim sheetref As Variant
    sheetref = Sheets("OpCo").Range("D5").Value
        Sheets(sheetref).Activate
End Sub

and then simply attach it to a button. If you want it to automatically take you to the sheet once you choose the option for the dropdown menu then try this in the the OpCo sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sheetref As Variant
    If Target.Address = "$D$5" Then
         sheetref = Sheets("OpCo").Range("D5").Value
            Sheets(sheetref).Activate
    End If

End Sub

HTH