easy question - drop down list to navigate

[dmcgimpsey]

This will be an easy question for probably anyone here, I am new t
Excel VB macros.

I would like to have a drop down list that has a selection of menu
options to link to - for example, open a specific spreadsheet;

is there a quick and easy way to do this?

Thanks in advance

D Mc

 

[Guest]

If you don't have to have a dropdown of menus and
you just want the user to go to different places,
sheets, websites, etc how about using hyperlinks on cells?

Otherwise, you'll have to program the dropdown menu
selections via macros to do more, I believe.

 

[dmcgimpsey]

That's what I have currently - I have 20 reports each linked to a cell
but that doubles when I go to 2004 reports, and so on.


I don't mind programming a drop down list, more or less need to find
skeleton...

Regard

 

[Jeff]

Try this. Add your listbox, right click for
"properties", set Linked Cell = A4, ListFillRange=
A1:B3, TextColumn=1;

With the following data on Sheet1 in cells A1:C3

Col A Col B Col C
Main Sheet1 G1
Test Sheet2 A1
Verify Sheet3 P20

Col A will show in the dropdown; Col B is the
destination Sheet, and Col C is the cell to go to.
Play with all this to make it fit for you.

Name this range "MenuRange"

Then add this macro code:
Private Sub ListBox1_Change()
x$ = Range("a4")
Y$ = Range("MenuRange").Find(x$, LookIn:=xlValues).Offset
(0, 1)
Z$ = Range("MenuRange").Find(x$, LookIn:=xlValues).Offset
(0, 2)
Worksheets(Y$).Activate
ActiveSheet.Range(Z$).Select
End Sub

good Luck!
Jeff

 

[ajw150]

Hi,

I have read your reply with interest but I get an "invalid outsid
procedure" which highlights on A4 when I try it?

Can you help?

Thanks

Andre