Drop Down List

[Guest]

Hi,

I'm trying to use a drop down list as a navigation system for a work sheet
that has over 40 charts in it. I have created a drop down list (at the top
of the worksheet above a frozen window split) that has entries populated with
the static reference names that I am using (ie the names of the charts).
What i want to do is allow the user to scroll down the list and click an item
in the list. As they click the item i want to have excel select and display
the area behind the chart that the item describes.
Eg A name in the list is Anual Total Expenditure. It is physically located
above cells b51:j61. If i use a simple hyperlink i would normally set it to
(b51:j61).

How do i make it such that if a user clicks on Anual Total Expenditure in
the drop down list, that excel will select b51:j61 as if the user had clicked
on a hyperlink?

Thanks

 

[gonecrazybacksoon]

Hi there

I did this using very simple code, but first you have to physically
create the hyperlinks.
OK, go to the linked cells for your drop down list, and hyperlink each
entry to where you want it to go exactly. Then, go to the code for the
drop down list and insert the following pieces of code


Private Sub ComboBox1_Change()
If ComboBox1.Text = "Annual Total Expenditure" Then
Range("Z3").Select ' This is where the linked cell for Annual Total
Expenditure is
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End If

If ComboBox1.Text = "Monthly Total Expenditure" Then
Range("Z1").Select ' This is where the linked cell for Monthly Total
Expenditure is
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End If
'
' Keep entering the If Then statements until you have all your
navigation complete
'
'

End Sub

Hope this makes sense.. PS: you will have to write a piece of code like
this for each hyperlink.
Sorry I don't know of a shorter way... I'm sure there is one, but this
works.
Let me know how you get on...

Sachin Singh

 

[Guest]

Fantastic. This is exactly what i needed. The code makes sense to me. I
was prepared to do this anyway which worked out well..

Thanks