Hyperlink to specific sheets in workbook based on drop down selection

[bbdubbs]

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]

Scroll Tab Into View in Large Workbooks
by SamT » Tue Mar 26, 2013 10:32 pm

[skywriter]

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]

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]

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]

You are magical! Thank you so much! This works so well!

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]

My pleasure, thanks for the feedback.