Drop Down list with hyperlinks to tabs in worksheet

[brefed15]

Hi,

I have a drop down list that I want to hyperlink to direct to specific tabs in a worksheet. I can't seem to find an easy solution to this. Any help would be greatly appreciated. Example of what I'm looking for below:

I have 2 drop down list items (Item 1 and Item 2). I want item 1 to direct me to "sheet 2" and item 2 to direct me to sheet 3.

Item 1 - (go to "sheet 2" when clicked)
Item 2 - (go to "sheet 3" when clicked)

Also, can i do this with hidden sheets (ex. if i decide to hide sheet 2 and sheet 3)?

Thanks,
Brett

 

[ravishankar]

Assuming cell C4 has drop down and A4 to A9 has items list and B4 to B9 has corresponding sheet names/numbers, right click on the sheet name (sheet1) and choose view code. paste the following codes

Private Sub Worksheet_Change(ByVal Target As Range)
Set KeyCells = Range("C4")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Sheets("sheet1").Cells(4, 4) = "=Vlookup(C4,$A$4:$B$9,2,false)"
Range("D4").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
Range("D4") & "!A1"
Range("D4").Hyperlinks(1).Follow
MsgBox "complete"
End If
End Sub

save it as .xlsm now if you choose from drop down, it finds corresponding sheet number from table A4 to B9 and goes to that sheet and says complete.
ravishankar