PDA

View Full Version : Solved: Automate Listbox hyperlink



Vikki_61
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:

Vikki_61

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"
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

Bob Phillips
12-08-2007, 04:05 PM
1.



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


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

Vikki_61
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:

Regards
Vikki_61:cloud9:

Bob Phillips
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.