using hyperlinks with a pull-down menu

[Jeff]

I am using Excel 2000 for a grade calculation project at my
university. The basic project involves all users inputting data on
the first sheet, then selecting whatever link is appropriate for them.
What I would like to do is have a pull-down menu they can use to go
to that appropriate link. Essentially, I am looking for some way to
do in Excel what can be done in HTML, where a user can select a choice
from the menu and be taken right to that page (in this case, a
different sheet in the same workbook). Can something like this be
done in Excel, and if so, how?

I realize it would be easier to simply have each link listed
separately, and create the specific hyperlinks to each sheet, but I
don't have the room it would take for that. A pull-down menu would
save a lot of needed space.

Thanks for any help you can give me!

Jeff

 

[Dave Peterson]

This won't work in xl97.

I put a list of URLs in a range of cells.
I added Data|validation to a cell (Say E1).

I assigned the valid entries to be that list.

Then I rightclicked on the worksheet tab with the data|validation cell and
selected view code. Then I pasted this in:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("e1")) Is Nothing Then Exit Sub

If Target.Value <> "" Then
ThisWorkbook.FollowHyperlink Address:=Target.Value
End If

End Sub

There are lots of options for .followhyperlink. You'll want to read more about
it in VBA's help to take full advantage.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Jeff]

Dave,

Thanks for the answer. I haven't had a chance to try it yet, and I am
going to have to learn about using macros first, but I will let you
know the results. I also found another possible answer in a different
thread yesterday, so between the 2, I think I should be okay.

Jeff