Active Hyperlinks in Dropdown / Validation

O

onyourfeet

New member
Joined
Oct 16, 2012
Messages
2
Reaction score
0
Points
0
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
 
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
 
[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!!
 
You must log in or register to reply here.
Back
Top