Links

    Hello everyone. I just discovered this site and I am excited to have it as a resource. Here's what I am working on doing:


    We would like to set up a Workbook in excel such that Sheet1 has a list
    of 100 items and then Sheet2-Sheet101 contain details on these items. We
    want to link them such that if we select item #25 on Sheet1, it will bring
    us right to Sheet25 to add information and save.


    I would really appreciate any info as to how to do this. Thanks!!


    Jeremy


    :guitar:

    Hi Jeremy,


    Welcome to the board :)


    Here's a procoedure that will create a table of contents with hyperlinks:


    <PRE&gt;<BR&gt;<FONT color=blue&gt;Sub </FONT&gt;Create_TOC()
    <BR&gt;<FONT color=blue&gt;Dim </FONT&gt;wbBook <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; Workbook</FONT&gt;
    <BR&gt;<FONT color=blue&gt;Dim </FONT&gt;wsActiveSheet <FONT color=blue&gt;As</FONT&gt; Worksheet, wsSheet <FONT color=blue&gt;As</FONT&gt; Worksheet
    <BR&gt;<FONT color=blue&gt;Dim </FONT&gt;lnRow <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; Long</FONT&gt;
    <BR&gt;
    <BR&gt;<FONT color=blue&gt;Set </FONT&gt;wbBook = ActiveWorkbook
    <BR&gt;
    <BR&gt;<FONT color=blue&gt;With </FONT&gt;Application
    <BR&gt; .DisplayAlerts =<FONT color=blue&gt; False</FONT&gt;
    <BR&gt; .ScreenUpdating =<FONT color=blue&gt; False</FONT&gt;
    <BR&gt;<FONT color=blue&gt;End With</FONT&gt;
    <BR&gt;
    <BR&gt;<FONT color=blue&gt;On Error</FONT&gt; <FONT color=blue&gt;Resume </FONT&gt;<FONT color=blue&gt;Next</FONT&gt;
    <BR&gt;<FONT color=blue&gt;With </FONT&gt;ActiveWorkbook
    <BR&gt; .Worksheets("Contents").Delete
    <BR&gt; .Worksheets.Add Before:=Worksheets(1)
    <BR&gt;<FONT color=blue&gt;End With</FONT&gt;
    <BR&gt;<FONT color=blue&gt;On Error</FONT&gt; <FONT color=blue&gt;GoTo</FONT&gt; 0
    <BR&gt;
    <BR&gt;<FONT color=blue&gt;Set </FONT&gt;wsActiveSheet = wbBook.ActiveSheet
    <BR&gt;
    <BR&gt;<FONT color=blue&gt;With </FONT&gt;wsActiveSheet
    <BR&gt; .Name = "Contents"
    <BR&gt; .<FONT color=blue&gt;Range</FONT&gt;("A1").Value = "Contents"
    <BR&gt;<FONT color=blue&gt;End With</FONT&gt;
    <BR&gt;
    <BR&gt;lnRow = 2
    <BR&gt;<FONT color=blue&gt;For </FONT&gt;Each wsSheet In wbBook.Worksheets
    <BR&gt; <FONT color=blue&gt;If </FONT&gt;wsSheet.Name <> wsActiveSheet.Name Then
    <BR&gt; wsActiveSheet.Hyperlinks.Add wsActiveSheet.Cells(lnRow, 1), "", _
    <BR&gt; SubAddress:="'" & wsSheet.Name & "'!A1", _
    <BR&gt; TextToDisplay:=wsSheet.Name
    <BR&gt; lnRow = lnRow + 1
    <BR&gt; <FONT color=blue&gt;End If</FONT&gt;
    <BR&gt;<FONT color=blue&gt;Next</FONT&gt; wsSheet
    <BR&gt;
    <BR&gt;Columns("A:A").EntireColumn.AutoFit
    <BR&gt;
    <BR&gt;<FONT color=blue&gt;With </FONT&gt;Application
    <BR&gt; .DisplayAlerts =<FONT color=blue&gt; True</FONT&gt;
    <BR&gt; .ScreenUpdating =<FONT color=blue&gt; True</FONT&gt;
    <BR&gt;<FONT color=blue&gt;End With</FONT&gt;
    <BR&gt;<FONT color=blue&gt;End Sub</FONT&gt;
    <BR&gt;<BR&gt;</PRE&gt;

    Put this on your first sheet.
    In this example the index is listed in Column A. If someone clicks on A1 and A1 contains 2 then they will be taken to Sheet2. If they click on cell A5 and A5 contains Whatever, they will be taken to sheet SheetWhatever.


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    col_desc = 1 '1=column A, 2=column B etc...
    this_row = ActiveCell.Row
    this_dest = "sheet" & Cells(this_row, col_desc).Value
    On Error Resume Next
    Select Case Target.Address
    Case Cells(this_row, col_desc).Address: Application.Goto Worksheets(this_dest).[a2]
    End Select
    End Sub

    Thanks so much for the extensive reply Dennis. I took a look at your website also, had to translate to English, though. (My sister may have been able to understand a little, she lived in Denmark for a year and a half and learned Danish) Well, the reply you posted looks like a type of subroutine programing. I've never written any programing in Excel so I don't know how to start. Is this subrouting something I enter directly in the cell?

    Egad, Thanks for the response you posted. It looks like what I am looking for, however I have never done any programing of this sort in excel. Is the subroutine you posted something I enter directly into a cell or how do I apply it?

    You have to put the code into the sheet1 VBA Module
    1) In Excel click on Tools-Macro-VB Editor
    2) In the VB Editor - Under Microsoft Excel Objects -Click on Sheet1 (or whatever your index sheet is)
    3) paste this code into the blank area on the right
    4) the code is set up for Column A but you can change this to another column (ie col_desc=3 = column C)
    5) anytime some clicks on a cell in that column it will take then to "Sheet"&cellvalue.
    6) you can remove the "sheet" if your sheets have descriptive names (not sheet1, sheet2 etc..)


    Hope this helps. (don't hesitate if you have more q's).
    Doug

    Doug,


    I put in column A on sheet1 in cells A1-A10 the words, Chemical1, Chemical2...Chemical10. Then I changed the name of sheet1 to "Index" and the made ten consecutive sheets labeled Chemical1 to Chemical10. Then I went through all the instructions you gave me and pasted the subroutine you gave me. Then I went back the the index and clicked on "Chemical1" but it didn't take me to the sheet I named "Chemical1" I must just be missing one step. Is there a compile button I need to press first? Please let me know what I need to do. Thanks!!

    J,
    1) Take the: "sheet" & out of my code. It is redundant.
    2) Make sure the macro is located in your "Index" VB sheet module.


    Doug (fingers crossed)

    Doug,


    You're the man, it worked!! :guitar: Slam! I should be able to transfer this to the actual file I'm working with but if I have trouble I'd like to be able to email you. Let me know if that's all right. Thanks again!


    Jeremy

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login