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

Sheets("SHEETLIST").Select
Range("A1").Select

For i = 1 To Workbooks("Stock Plan.xls").Worksheets.Count
   ActiveCell.Value = ActiveWorkbook.Worksheets(i).Name
   ActiveCell.Offset(1, 0).Select
Next

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

if activesheet.name = "SHEETLIST" then

sheetname = activecell.value
workbooks("Master Plan.xls").activate
worksheets(sheetname).activate

endif

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

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Mid(ActiveCell.Address, 2, 1) = "B" Then
    Call open_sheet
End If
End Sub

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.

Sub open_sheet()

   sheet_name = ActiveSheet.Name
    open_name = ActiveCell.Value

    Select Case sheet_name

    Case "Cabinet"
    Workbooks("Stock Plan Cabinet.xlsx").Activate
    Worksheets("sheet" & open_name).Select

    Case "Raw Material"
    Workbooks("Stock Plan Raw Material.xlsx").Activate
    Worksheets("sheet" & open_name).Select

    Case "Store"
    Workbooks("Stock Plan Store.xlsx").Activate
    Worksheets("sheet" & open_name).Select

    End Select

End Sub

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.