Solved: Automate Listbox hyperlink

[Vikki_61]

hi folks
Attached is my Demo test file.
In Worksheet 1 you will see a dropdown picklist - it has been acheived by Using Data/Validation select list. However while it does work - to change the hyperlink what I would prefer it to do is as a user makes their choice/pick from the list they are taken straight away to the worksheet they have chosen -without the the need for the user to actually also have to click on the hyperlink then going to the worksheet. So what I am trying to achieve is one less interaction needed from the user.

Also how can I get the text in the listbox dropdown to be a slightly bigger font size so ti is easier to read for the user.

Thanks in advance

Vikki_61

[Simon Lloyd]

Try this in your worksheet change module, no need for hyperlinks it will work of the validated cell!
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address <> "$F$23" Then Exit Sub
Select Case Target

Case "Disney Land"
Sheets(3).Activate
Case "Pleasure Island"
Sheets(4).Activate
Case "Water World"
Sheets(5).Activate
Case "Fun Park"
Sheets(6).Activate
End Select

End If

End Sub
[/VBA]

[Bob Phillips]

1.

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F23")) Is Nothing Then

On Error Resume Next
Target.Hyperlinks(1).Delete
On Error GoTo 0

Select Case Target

Case "Disney Land": Application.Goto Worksheets("3. DISNEYLAND").Range("A1")

Case "Pleasure Island": Application.Goto Worksheets("3. PLEASURE ISLAND").Range("A1")

Case "Water World": Application.Goto Worksheets("4. WATER WORLD").Range("A1")

Case "Fun Park": Application.Goto Worksheets("5. FUN PARK").Range("A1")
End Select
End If


End Sub
[/vba]

2. See http://www.contextures.com/xlDataVal08.html#Font

[Vikki_61]

Thankyou Simon Lloyd - I got a debug but fix it by removing the end if then it worked great.

Thankyou again SIR XLD your code worked great first up...no Problems

Regards
Vikki_61

[Bob Phillips]

I am confused as tgo where this fits with the original question, they seem to be going in different directions.