none
dropdown menu linking RRS feed

  • Question

  • Hey,

    wants to apologize for my bad English but will try to be clear
    I just started with vba and had a question but do not manage to link a dropdown menu to a tab

    have made a dropdown menu (on the tab that I have called a calendar) with all months of the year in now I would like to be in vba

    Obtain if I click the January choice that it goes to the January tab if I click February to February etc. etc ...

    how can I do this in an easy way?

    thank you verry mutch

    Paul

    Monday, February 26, 2018 3:24 PM

All replies

  • 1) Copy this code.
    2) Right-Click the sheet tab of the sheet "calendar"
    3) Select "View Code"
    4) Paste the code into the window that appears.
    5) Save the file as a macro-enabled .xlsm file.
    6) Make changes as needed to the cell addresses - as written the dropdown needs to be a DV list on cell C4, and cell A1 will be selected when the monthly sheet is chosen.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address <> "$C$4" Then Exit Sub
        If Target.Value = "" Then Exit Sub

        'Turn off events to keep out of loops
        Application.EnableEvents = False

        With Worksheets(Target.Value)
            .Activate
            .Range("A1").Select
        End With

        Application.EnableEvents = True
    End Sub

        
    Monday, February 26, 2018 4:45 PM
  •      

    H
    i,

    Suppose that DropDown list is in Cell A1, code is like the below.


    [Code: VBA] put this code in sheet [calendar]

    Private Sub Worksheet_Change(ByVal Target As Range)
        If (Application.Intersect(Target, Range("A1")) Is Nothing) Then
            ' --- do nothing (nothing changed in A1)
        Else
            ' --- changed
            If (Range("A1").Value = "") Then
                Exit Sub
            Else
                Sheets(Range("A1").Value).Select
            End If
        End If
    End Sub

    Regards,


    Ashidacchi

    • Edited by Ashidacchi Tuesday, February 27, 2018 1:14 AM
    • Proposed as answer by Terry Xu - MSFT Tuesday, February 27, 2018 3:16 AM
    Tuesday, February 27, 2018 1:07 AM