Results 1 to 3 of 3

Thread: Active Hyperlinks in Dropdown / Validation

  1. #1

    Active Hyperlinks in Dropdown / Validation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi - I'm sure there's a really simple solution to this but I'm going boss-eyed trying to work it out.

    I have a workbook with approx 30 worksheets in it. I'm attempting to put a cover sheet on that includes a dropdown / validation cell (using a a range of values that are the names of the other worksheets). All I want to be able to do is go to the relevant worksheet when I select a value in the dropdown menu.

    eg When I select sheet 10 name in the dropdown, I'd like to go to sheet 10, cell A1 automatically.

    I'm fine with creating the data validation range, I'm fine with creating hyperlinks but I can't get active hyperlinks in the dropdown menu itself - is there a way of doing it via VBA? There appear, to me anyway, too many variables for me to use IF statements so I'm hoping to be able to use If / Else etc in VBA but I am currently struggling. Something along the lines of this code but with up to 30 sheets?

    Private Sub ComboBox1_Change()
    Sheet2.Activate
    Sheet2.Range("E1").Offset(ComboBox1.ListIndex).Select
    End Sub

    I'm on a shared network PC so have been unable to upload the file so I hope my ramblings suffice.

    Any assistance appreciated

  2. #2
    1. Change A1 in code below to match the cell where your dropdown list resides.

    2. At Bottom ..... right click on the Tab name (or sheet name) that contains your dropdown list -> View Code -> paste below into resulting window:




    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Application.ScreenUpdating = False
        Sheets(Range("A1").Value).Select
        Application.ScreenUpdating = True
    End If
    End Sub

  3. #3

    [SOLVED] Active Hyperlinks in Dropdown / Validation

    Just the job mate - thanks for the quick reply.

    This code will be a life-saver in the work I do!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •