Drop Down List hyperlink to sheets in another workbook

Hi all

I have two workbooks: one is Master Plan and another is Stock Plan. The Stock Plan contains 500 sheets. What i want is, when I click the drop down list (which is located in the Master Plan) it will list all the 500 sheets and if i choose a sheet (let say sheet 387) it automatically hyperlink to that sheet and open that sheet. Is there any way to do that? Because right now, i'm using manual method by hyperlink the 500 sheets one by one and it really take days to finish.

i'm newbie and this really drive me crazy.

Imran

To navigate a drop down list with 500 options could be a mission...but maybe this may get you started which is more simple to create. It will generate a list with all the sheetnames and when you double click on a sheetname it'll open that sheet.

Create a sheet with the name SHEETLIST or whatever in your Master Plan and run this code in your Master Plan

Please Login or Register  to view this content.

then in your Master Plan's Workbook Module create a SheetBeforeDoubleClick function with the following code

Please Login or Register  to view this content.

Dear Bongaan

Thanks for your reply, but i'm not very good in macro. I think that your code does not suitable with what i need. hope you can see the dummy that i attach.

Thanks a lot

cheers

Will it work for you if you are able to double click on the No. in column B in your Master Plan and it opens the correct sheet?

yeah. that is what i do it right now. i hyperlink each number to its own sheet and it really takes a lot of time. i try to do something like this http://www.youtube.com/watch?v=p_77cZ1Vyl4 and i try to implement with my condition but it did not work.

I unfortunately are not able to watch youtube.

When in your Master Plan, press Alt & F11 to open the Visual Basic editor, then select ThisWorkbook under Master Plan.xlsx top left. Paste this code under your ThisWorkbook

Please Login or Register  to view this content.

Then right click on the same ThisWorkbook and choose Insert > Module, a new module will appear under modules. Copy the following code into the module.

Please Login or Register  to view this content.

Now when you double click on the number in the Master Plan then it will open that sheet. Try to understand this few lines of code, it's not that difficult, so that you can add to it if you add more files or change the names of your files.

The code in the post of 08-30-2012, 11:39 AM is the code you should use.

@ imran91

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

Dear Boongan

I already found out how to do that by using formula: =HYPERLINK("[Stock Plan]"&$A$1&"!$A$1","CLICK HERE"). So, if i type the sheet name in A1, it will directly go to that sheet. Anyway, thank you very much for your ideas.