View Full Version : Solved: Automate Listbox hyperlink

12-08-2007, 03:05 PM
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 :cloud9:


Simon Lloyd
12-08-2007, 03:58 PM
Try this in your worksheet change module, no need for hyperlinks it will work of the validated cell!

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"
Case "Pleasure Island"
Case "Water World"
Case "Fun Park"
End Select

End If

End Sub

12-08-2007, 04:05 PM

Private Sub Worksheet_Change(ByVal Target As Range)

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

On Error Resume Next
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

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

12-08-2007, 07:11 PM
Thankyou Simon Lloyd - I got a debug but fix it by removing the end if then it worked great. :friends:

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


12-09-2007, 03:32 AM
I am confused as tgo where this fits with the original question, they seem to be going in different directions.