Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Aug 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2000 hyperlinks (2000)

    I am trying to create a "Contents" page in a workbook, which lists the Subject matter of each worksheet, and includes a Hyperlink to that sheet.

    It works fine if all of the sheets have names beginning with alphabetical characters, but when the sheetname is something like 006, 0r 3.06, the hyperlinks don't appear! <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    I have included the VBA code I use below:

    Sub Table_Of_Contents()
    numsheets = Sheets.Count
    Sheets("Contents").Select
    Range("a10").Select

    'Insert Hyperlinks
    For N = 2 To numsheets
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & Sheets(N).Name & "'!D3", TextToDisplay:="'" & Mid((Sheets(N).Name), 6, 99)
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Sheets(N).Range("J5")
    ActiveCell.Offset(1, -1).Select
    Next

    End Sub


    I would welcome any help!

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 2000 hyperlinks (2000)

    You set the display text for the hyperlink to Mid((Sheets(N).Name), 6, 99), i.e. omit the first five characters from the sheet name. If the sheet name is less than six characters long, the display text will be empty! this is the case for names such as '006' or '3.06'.

  3. #3
    New Lounger
    Join Date
    Aug 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 hyperlinks (2000)

    Hans,

    Sorry about that. The code that I have posted is actaully trimmed down.

    The MID function I was using because I have set up a routine to name all of the worksheets "Room 003" and "Room 3.05" to workaround the problem I am having.

    I have removed that item, and as is common, I am having trouble replicating my previous problem.

    Thanks for your input.

    Consider it solved!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •