PDA

View Full Version : Hyperlink to specific sheets in workbook based on drop down selection



bbdubbs
05-15-2016, 03:12 PM
Hello!

I am looking for VBA code to accomplish something. Im not well versed in VBA at all So I think I need some help!
Im using Data validation to create drop down lists. I have 7 different selection options and corresponding worksheets within the workbook for those selections.

If I have A1 set as Cookies and I want the next cell B1 to be a hyperlink to the corresponding Cookie Sheet.

so my drop down list could be and I have sheets named the same within my work book.

Cookies
Cakes
Chocolate
Candy
Frozen
Baked
Fruit

Can anyone help me with this? I am in over my head!

SamT
05-15-2016, 05:37 PM
Scroll Tab Into View in Large Workbooks (http://www.excelkey.com/forum/viewtopic.php?f=5&t=3305)
by SamT (http://www.excelkey.com/forum/memberlist.php?mode=viewprofile&u=6859) » Tue Mar 26, 2013 10:32 pm

skywriter
05-15-2016, 06:13 PM
Right click on the tab for the worksheet with the drop down and choose view code.

Paste this code into the worksheet module that appears.

This assumes you don't have existing change event code in that module.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If Not Evaluate("ISREF('" & Target.Value & "'!A1)") Then
MsgBox "There is no sheet named " & Target.Value & " and a hyperlink will not be created.", vbOKOnly
Exit Sub
End If
Application.EnableEvents = False
Me.Hyperlinks.Add Anchor:=Range("B1"), Address:="", _
SubAddress:="'" & Target.Value & "'" & "!A1", TextToDisplay:=Target.Value & " A1"
Application.EnableEvents = True
End Sub

bbdubbs
05-16-2016, 08:38 AM
This worked perfectly!
If I wanted to do the same thing for B2 and B3 would that change the code in reference to what they chose in A2 and A3 ? I tried to paste what you sent me and change the cell value in the code but it is not working.



Right click on the tab for the worksheet with the drop down and choose view code.

Paste this code into the worksheet module that appears.

This assumes you don't have existing change event code in that module.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If Not Evaluate("ISREF('" & Target.Value & "'!A1)") Then
MsgBox "There is no sheet named " & Target.Value & " and a hyperlink will not be created.", vbOKOnly
Exit Sub
End If
Application.EnableEvents = False
Me.Hyperlinks.Add Anchor:=Range("B1"), Address:="", _
SubAddress:="'" & Target.Value & "'" & "!A1", TextToDisplay:=Target.Value & " A1"
Application.EnableEvents = True
End Sub

skywriter
05-16-2016, 09:21 AM
This works for A2 to A3, see the second line for changing to another range.


Private Sub Worksheet_Change(ByVal Target As Range)
' Works for range A2 to A3, change the range in the line below to suit your needs.
If Intersect(Target, Range("A2:A3")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Not Evaluate("ISREF('" & Target.Value & "'!A1)") Then
MsgBox "There is no sheet named " & Target.Value & " and a hyperlink will not be created.", vbOKOnly
Exit Sub
End If
Application.EnableEvents = False
Me.Hyperlinks.Add Anchor:=Target.Offset(, 1), Address:="", _
SubAddress:="'" & Target.Value & "'" & "!A1", TextToDisplay:=Target.Value & " A1"
Application.EnableEvents = True
End Sub

bbdubbs
05-16-2016, 10:11 AM
You are magical! Thank you so much! This works so well! :hi:

This works for A2 to A3, see the second line for changing to another range.


Private Sub Worksheet_Change(ByVal Target As Range)
' Works for range A2 to A3, change the range in the line below to suit your needs.
If Intersect(Target, Range("A2:A3")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Not Evaluate("ISREF('" & Target.Value & "'!A1)") Then
MsgBox "There is no sheet named " & Target.Value & " and a hyperlink will not be created.", vbOKOnly
Exit Sub
End If
Application.EnableEvents = False
Me.Hyperlinks.Add Anchor:=Target.Offset(, 1), Address:="", _
SubAddress:="'" & Target.Value & "'" & "!A1", TextToDisplay:=Target.Value & " A1"
Application.EnableEvents = True
End Sub

skywriter
05-16-2016, 10:27 AM
My pleasure, thanks for the feedback.
:beerchug: