Build Table of Contents, similar listings, working with Hyperlinks

 

This page contains some VBA macros.  Assistance to Install a Macro or User Defined Function  on my Formula page.

Coding

Coding for macros BuildTOC, SortAllSheets, DelHyperLinks, MakeHyperLinks, RunSubFromActiveCell, LinkFix,  LinkFix_GoTo,  MakeHTML_Link
and the user defined functions URL and MSKBQ can be found at
    http://www.mvps.org/dmcritchie/excel/code/buildtoc.txt

Separate coding for ListFunctionsAndSubs can be found at
   http://www.mvps.org/dmcritchie/excel/code/listfsubs.txt
   See internal note concerning "Microsoft Visual Basic For Application Extensibility" required in the VBE

and the macros GoToCell, GoToHyperlink, GoToSheet,  GoToNextSheet,  GoToPrevSheet,
     GoToSub, ShowTopLeft and ShowTopLeft5. can be found at
    http://www.mvps.org/dmcritchie/excel/code/gotostuff.txt   also see Toolbar and Menu examples on Toolbars page.

BuildTOC

Creating a Table of Contents with hyperlinks to the sheets in the workbook is simple using the BuildTOC() macro.  XL2000 provides hypertext links in your Excel spreadsheets.  The links generated here will be to sheets within your workbook.

Included are Worksheet, Module, and Dialog which will be sorted descending so that the worksheets appear first.  Sheet Tabs on the otherhand are sorted without regard to type, do not include modules, and the collating sequence is slightly different for special characters such as a tilde (~).

 

A B C D 1   Select a3 and create or    redo with BuildTOC()   2 Worksheet Type CodeName   3 $$TOC Worksheet Sheet94   4 ~HELP~ Worksheet Sheet85   5 1999-01-31 Worksheet Sheet6   6 1999-04-04 Worksheet Sheet7   7 1999-06-15 Worksheet Sheet1   8 1st 2nd 3rd Worksheet Sheet8   9 4hdr rows Worksheet Sheet9   11 Abuse Worksheet Sheet10   12 AddinsSheet Worksheet Sheet11   13 Address Worksheet Sheet12

Referencing a Worksheet Referencing the Worksheet name seen on the tabs at the bottom   Worksheets("Sheet3").Select Sheets("Sheet3").Select   Referencing the sheet name (code name) that is located in the sheets properties (in the Properties window) in VBE.   Sheet3.Select Sheet(3).Select   Referencing the sheet name as the activesheet ActiveSheet.Range("A1").Value = 99   The Table shown on the left is typical of the generated results from the BuildTOC described on this page.

Although this page is really mainly about hyperlinks.  You might find the following formula version interesting.  You would see nothing, A5 or A8 which would correspond to no link, link to A5 or link to A8 all depending on value in A1.  This is all one long worksheet formula, no spaces needed...

=IF(TRIM(A1)="","",IF(A1<5,HYPERLINK("[vlookup.xls]Sheet31!A5","a5"),HYPERLINK("[vlookup.xls]Sheet31!A8","a8")))

The above Table will be redone in the following format

	A	B	C	D	E	F	G
1	Select A3 and create or redo with BuildTOC()
2	Worksheet	Type	CodeName	lastcell	cells	scrollarea	Printarea
3	Contractors	Worksheet	Sheet5	T168	3,360		$A$1:$I$168
4	Insulation	Worksheet	Sheet2	Y139	3,475
5	Introduction	Worksheet	Sheet12	CL946	85,140
6	Misbehaving	Worksheet	Sheet20	M65536	851,968
7	Reporting	Worksheet	Sheet8	AQ81	3,483		$S$1:$AQ$59
8	Requirements	Worksheet	Sheet9	AL93	3,534
9	Sheet1	Worksheet	Sheet32	AP59	2,478
10	Sub Contract	Worksheet	Sheet11	AL93	3,534		$A$1:$S$132
11	Suppliers - ALL	Worksheet	Sheet7	AC2411	69,919
12	Suppliers - Major	Worksheet	Sheet10	AL93	3,534		$1:$65536
13	Vendor List	Worksheet	Sheet6	AX1094	54,700	$A1:M79	$A$1:$Q$79

Also have an auxiliary page on how to Build a Summary Sheet for sheets that have exactly the same consistent format.

Coding for BuildTOC() macro

If you need instructions to install a macro see my formula page.

Update - update - update - update - update - update - update - update - update - update - update - update.
 
This macro has been redesigned to work in XL95, XL97, and XL2000. 
In XL95 you cannot use hyperlinks but you can use the GoToSheet subroutine included.

Chip Pearson has noted in a newsgroup posting that having more than a couple of dozen hyperlinks of the object kind can have a devastating effect on workbook performance.  So the BuildTOC macro has been changed to create the =HYPERLINK(target,description) type instead of object hyperlinks.  Also note that the object hyperlinks can be removed with DelHyperlinks() macro described on this web page.
 
Update - update - update - update - update - update - update - update - update - update - update - update.

Sub BuildTOC()
  'listed from active cell down 7-cols --  DMcRitchie 1999-08-14 2000-09-05
  Dim iSheet As Integer, iBefore As Integer
  Dim sSheetName As String, sActiveCell As String
  Dim cRow As Long, cCol As Long, cSht As Integer
  Dim lastcell
  Dim qSht As String
  Dim mg As String
  Dim rg As Range
  Dim CRLF As String
  Dim Reply As Variant
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  cRow = ActiveCell.Row
  cCol = ActiveCell.Column
  sSheetName = UCase(ActiveSheet.Name)
  sActiveCell = UCase(ActiveCell.Value)
  mg = ""
  CRLF = Chr(10)  'Actually just CR
  Set rg = Range(Cells(cRow, cCol), Cells(cRow - 1 + ActiveWorkbook.Sheets.Count, cCol + 7))
  rg.Select
  If sSheetName <> "$$TOC" Then mg = mg & "Sheetname is not $$TOC" & CRLF
  If sActiveCell <> "$$TOC" Then mg = mg & "Selected cell value is not $$TOC" & CRLF
  If mg <> "" Then
     mg = "Warning BuildTOC will destructively rewrite the selected area" _
     & CRLF & CRLF & mg & CRLF & "Press OK to proceed, " _
      & "the affected area will be rewritten, or" & CRLF & _
      "Press CANCEL to check area then reinvoke this macro (BuildTOC)"
     Application.ScreenUpdating = True  'make range visible
     Reply = MsgBox(mg, vbOKCancel, "Create TOC for " & ActiveWorkbook.Sheets.Count _
      & " items in workbook" & Chr(10) & "revised will now occupy up to 10 columns")
     Application.ScreenUpdating = False
     If Reply <> 1 Then GoTo AbortCode
  End If
  rg.Clear      'Clear out any previous hyperlinks, fonts, etc in the area
  For cSht = 1 To ActiveWorkbook.Sheets.Count
     Cells(cRow - 1 + cSht, cCol) = "'" & Sheets(cSht).Name
     If TypeName(Sheets(cSht)) = "Worksheet" Then
        'hypName = "'" & Sheets(csht).Name
        ' qSht = Replace(Sheets(cSht).Name, """", """""") -- replace not in XL97
        qSht = Application.Substitute(Sheets(cSht).Name, """", """""")
        If Application.Version < "8.0" Then
          '-- use next line for XL95
          Cells(cRow - 1 + cSht, cCol + 2) = "'" & Sheets(cSht).Name  'XL95
        Else
          '-- Only for XL97, XL98, XL2000 -- will create hyperlink & codename
          Cells(cRow - 1 + cSht, cCol + 2) = "'" & Sheets(cSht).CodeName

          '--- excel is not handling lots of objects well ---
          'ActiveSheet.Hyperlinks.Add Anchor:=Cells(cRow - 1 + cSht, cCol), _
          '  Address:="", SubAddress:="'" & Sheets(cSht).Name & "'!A1"
          '--- so will use the HYPERLINK formula instead ---
          '--- =HYPERLINK("[VLOOKUP.XLS]'$$TOC'!A1","$$TOC")
          ActiveSheet.Cells(cRow - 1 + cSht, cCol).Formula = _
            "=hyperlink(""[" & ActiveWorkbook.Name _
            & "]'" & qSht & "'!A1"",""" & qSht & """)"
        End If
     Else
       Cells(cRow - 1 + cSht, cCol + 2) = "'" & Sheets(cSht).Name
     End If
     Cells(cRow - 1 + cSht, cCol + 1) = TypeName(Sheets(cSht))
    ' -- activate next line to include content of cell A1 for each sheet
    ' Cells(cRow - 1 + csht, cCol + 3) = Sheets(Sheets(csht).Name).Range("A1").Value
     On Error Resume Next
     Cells(cRow - 1 + cSht, cCol + 6) = Sheets(cSht).ScrollArea '.Address(0, 0)
     Cells(cRow - 1 + cSht, cCol + 7) = Sheets(cSht).PageSetup.PrintArea
     If TypeName(Sheets(cSht)) <> "Worksheet" Then GoTo byp7
     Set lastcell = Sheets(cSht).Cells.SpecialCells(xlLastCell)
     Cells(cRow - 1 + cSht, cCol + 4) = lastcell.Address(0, 0)
     Cells(cRow - 1 + cSht, cCol + 5) = lastcell.Column * lastcell.Row
byp7: 'xxx
     On Error GoTo 0
  Next cSht

  'Now sort the results:  2. Type(D), 1. Name (A), 3. module(unsorted)
  rg.Sort Key1:=rg.Cells(1, 2), Order1:=xlDescending, Key2:=rg.Cells(1, 1) _
      , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
  rg.Columns.AutoFit
  rg.Select           'optional
  'if cells above range are blank want these headers
  ' Worksheet,   Type,    codename
  If cRow > 1 Then
     If "" = Trim(Cells(cRow - 1, cCol) & Cells(cRow - 1, cCol + 1) & Cells(cRow - 1, cCol + 2)) Then
        Cells(cRow - 1, cCol) = "Worksheet"
        Cells(cRow - 1, cCol + 1) = "Type"
        Cells(cRow - 1, cCol + 2) = "CodeName"
        Cells(cRow - 1, cCol + 3) = "[opt.]"
        Cells(cRow - 1, cCol + 4) = "Lastcell"
        Cells(cRow - 1, cCol + 5) = "cells"
        Cells(cRow - 1, cCol + 6) = "ScrollArea"
        Cells(cRow - 1, cCol + 7) = "PrintArea"
     End If
  End If
  Application.ScreenUpdating = True
  Reply = MsgBox("Table of Contents created." & CRLF & CRLF & _
     "Would you like the tabs in workbook also sorted", _
     vbOKCancel, "Option to Sort " & ActiveWorkbook.Sheets.Count _
     & " tabs in workbook")
  Application.ScreenUpdating = False
  If Reply = 1 Then SortALLSheets  'Invoke macro to Sort Sheet Tabs
  Sheets(sSheetName).Activate
AbortCode:
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub
Sub BuildTOC_A3()
   Cells(3, 1).Select
   BuildTOC
End Sub
Sub SortALLSheets()
  'sort sheets within a workbook in Excel 7 -- Bill Manville
  'modified to sort all sheets instead of just worksheets
    Dim iSheet As Integer, iBefore As Integer
  For iSheet = 1 To ActiveWorkbook.Sheets.Count
    Sheets(iSheet).Visible = True
    For iBefore = 1 To iSheet - 1
      If UCase(Sheets(iBefore).Name) > UCase(Sheets(iSheet).Name) Then
        ActiveWorkbook.Sheets(iSheet).Move Before:=ActiveWorkbook.Sheets(iBefore)
        Exit For
      End If
    Next iBefore
  Next iSheet
End Sub

If using XL97 or earlier see compatibility notes concerning Replace.

The following suggestion has been made by Dave Peterson (not posted 2001-03-22) who says that renaming the book messes up such references as:
   =HYPERLINK("[VLOOKUP.XLS]'sumproduct'!A1","sumproduct")

Instead of:

       ActiveSheet.Cells(cRow - 1 + cSht, cCol).Formula = _
            "=hyperlink(""[" & ActiveWorkbook.Name _
            & "]'" & qSht & "'!A1"",""" & qSht & """)"

       myformula = MID(CELL(""filename"",$A$1),FIND(""" _
             & "["",CELL(""filename"",$A$1)),FIND(""]""," _
             & "CELL(""filename"",$A$1))-FIND(""[""," _
             & "CELL(""filename"",$A$1))+1)"
      ActiveSheet.Cells(cRow - 1 + vis_sht, cCol).Formula = _
           "=hyperlink(" & myformula & "&""" _
           & "'" & qSht & "'!a1"",""" & qSht & """)"

Identify Large Sheets in a Workbook

Other Useful/Interesting Information that could be included

Create a button on the worksheet to invoke BuildTOC

Sub BuildTOC_A3()
   Cells(3, 1).Select   'Selects cell A3
   BuildTOC
End Sub

1. Color background of cell with 4 lines shown above.
2. Ceate picture: select cell, hold Shift + Edit --> Copy picture
3. Paste with Ctrl+V
4. With the picture selected, RtClick --> Format picture --> Properties --> move and size with cells
5. With the picture selected, RtClick --> Assign Macro --> BuildTOC_A3

Including Additional Information in Other Columns

Each time you run BuildTOC you will rearrange the listing of sheetnames, so if you wanted to include additional information you would need to pull that information in from another sheet or rename of modify the BuildTOC macro.

You could have another sheet and use VLOOKUP, or if the information is in a specific location on the $$TOC indicated sheet use INDIRECT.

You could use INDIRECT and get information off of the indicated sheet.
   =INDIRECT("'"'&A11&"'!A1")   -- single quotes are enclosed in double quotes
The above formula might equate to  ='sheet1'!A1

You could use VLOOKUP to get associated information from another sheet
   =VLOOKUP(A11,table,1,false)   i.e. sheetname in column 1 of table
   =VLOOKUP(A11,table,2,false)   i.e. lastname of person in column 2 of table

False, in VLOOKUP, requires an exact match and the table does not need to be sorted.  You do not want to use true because that will return an exact match or an approximate match.

For more information on VLOOKUP see HELP and my vlookup page
  VLOOKUP Worksheet Function

SortAllSheets -- The following code will sort sheet tabs

Sub SortALLSheets()
  'sort sheets within a workbook in Excel 7 -- Bill Manville
  'modified to sort all sheets instead of just worksheets
    Dim iSheet As Integer, iBefore As Integer
  For iSheet = 1 To ActiveWorkbook.Sheets.Count
    Sheets(iSheet).Visible = True
    For iBefore = 1 To iSheet - 1
      If UCase(Sheets(iBefore).Name) > UCase(Sheets(iSheet).Name) Then
        ActiveWorkbook.Sheets(iSheet).Move Before:=ActiveWorkbook.Sheets(iBefore)
        Exit For
      End If
    Next iBefore
  Next iSheet
End Sub

Looping through all of the worksheets (SheetnamesInA1)

  Sub SheetnamesInA1()
    '2000-04-20
    Dim iSheet as Long
    Application.ScreenUpdating = False
    For iSheet = 1 To ActiveWorkbook.WorkSheets.Count
     WorkSheets(iSheet).cells(1,1) = "'" & WorkSheets(iSheet).name
    Next iSheet
    Application.ScreenUpdating = True
  End Sub

Converting a selection containing sheetnames to hyperlinks (trevor001)

Sub trevor001()
  Dim Cell  As Range
  For Each Cell In Selection
  ActiveSheet.Hyperlinks.Add Anchor:=Cells(Cell.Row, Cell.Column), _
            Address:="", SubAddress:="'" & Sheets(Cell.Value).Name & "'!A1"
  Next Cell
End Sub

Names of WorkSheets across columns from the activecell

Sub SheetNamesAcrossColumns()
  Dim iSheet As Integer
  For iSheet = 1 To ActiveWorkbook.WorkSheets.Count
    ActiveCell.offset(0, iSheet - 1) = WorkSheets(iSheet).Name
  Next iSheet
End Sub

   =INDIRECT( "'" & D1 & "'" & "!$a$4")

GoToSub -- Display Subroutine named in selected cell

'Display Subroutine or Function named in selected cell
Sub GoToSub()
   'David McRitchie 1999-11-12 rev. 2000-04-13
   'http://www.mvps.org/dmcritchie/excel/buildtoc.htm
   On Error GoTo notfound   'formerly named GoToSubroutine
   Dim i As Integer
   Application.Goto Reference:=ActiveCell.Value
   Exit Sub
notfound:   
   On Error GoTo notfound2
   If Left(ActiveCell.Formula, 1) = "=" Then
      For i = 1 To Len(ActiveCell.Formula)
        If Mid(ActiveCell.Formula, i, 1) = "(" Then
          MsgBox Mid(ActiveCell.Formula, 2, i - 2)
          Application.Goto Reference:=Mid(ActiveCell.Formula, 2, i - 2)
          Exit Sub
        End If
      Next i
      MsgBox Mid(ActiveCell.Formula, 2, i - 2) _
        & "was not found as a User Defined Function, " _
        & "verify with Paste Function Wizard [fx]"
   End If
notfound2:  
   On Error Resume Next
   MsgBox "Procedure or Function " & ActiveCell.Value _
      & " is not available, try ALT+F8 to find Sub, or [fx] to find UDF"
End Sub

RunSubFromActiveCell

Sub RunSubFromActiveCell()
   Application.Run ActiveCell.Value
End Sub

GoToSheet -- Select the Sheet named in selected cell

Sub GoToSheet()
  'David McRitchie
  On Error Resume Next
  If Worksheets(ActiveCell.Value) Is Nothing Then
    MsgBox ActiveCell.Value & " -- sheet does not exist"
  Else
    Sheets(ActiveCell.Value).Select
  End If
  On Error GoTo 0
End Sub

To select a sheet with a known name in VBA

     Application.Goto Reference:=Range("'Radio Stations'" & "!a1")

For a variation of GoToSheet, see GoToCustomerSheet, which uses an input box to ask for a customer which equates to a sheetname.

Summary of possible steps:   (Gary Brown)
   Workbooks.Open Filename:="C:\Temp\Myfile.xls"
   Windows("Myfile.xls").Visible = True
   Application.Goto Reference:="sheetaa!C14"

Place active cell in top left of viewable area (ShowTopLeft, ShowTopLeft5)

'Active cell repositioned to Top also showing 5 cells to left
'   remains on same sheet
Sub ShowTopLeft5()
        Dim caddr As String
    caddr = Selection.Address
    Application.Goto Reference:=Cells(ActiveCell.Row, _
        Application.WorksheetFunction _
        .Max(1, ActiveCell.Column - 5)), Scroll:=True
    Range(caddr).Select
End Sub

'repostition active cell to Top Left corner
'   remains on same sheet
Sub ShowTopLeft()
    Application.Goto Reference:=Range(ActiveCell.Address), Scroll:=True
End Sub

Subroutine and Function Table for Open Worksheets (ListFunctionsAndSubs)

ListFunctionsAndSubs

	A	B	C	D	E	F	G	H
1	Book	Module	Name	Type	Beg#	Lns	###	chk
2	testng2k.xls	Module4	AltRowDelete	SubRoutine	35	8	132
3	personal.xls	Module1	auto_Open	SubRoutine	1	7	1	Dup
4	testng2k.xls	Module1	auto_open	SubRoutine	2	13	24	Dup
5	testng2k.xls	Module19	Beep2	SubRoutine	7	34	155
6	testng2k.xls	Module13	GetCellFormat	SubRoutine	18	11	142
7	personal.xls	Module1	GetFormat	Function	31	6	5	Dup
8	testng2k.xls	ModuleF	GetFormat	Function	24	6	71	Dup
9	personal.xls	Module1	MarkSepAreas	SubRoutine	72	14	12
10	testng2k.xls	Module1	Upper_Case	SubRoutine	70	15	29
11	personal.xls	Module1	UseFormula	Function	8	6	2	Dup
12	testng2k.xls	ModuleF	UseFormula	Function	1	6	68	Dup
13	testng2k.xls	Module1	XL2HTML	SubRoutine	174	54	35
14	testng2k.xls	mod.XL2HMLx	XL2HTMLx	SubRoutine	8	97	166

Separate coding for ListFunctionsAndSubs can be found here, references to similar material can be found in the Related area of this page, such as Chip Pearson's "Coding for the VBE".

A companion piece of code that is used, since I could not establish hyperlinks to subroutines and functions within the VBA editor, and it is shown below and can be assigned to a single button.  You may select any cell on the row to get to the code.  Be forewarned that each time you invoke the subroutine another window page is stored so you might want to use the little [x] to get out of that code module each time (not talking about the big [X] to get out of the VBA editor.  ShowSubOrFunction is specialized to this table see GoToSub for a more generic macro.

Sub ShowSubOrFunction()
'--Companion to ListFunctionsAndSubs, for lack of a hypertext solution.
'--Application.Goto Reference:=WBName & "!" & Subname
Application.Goto Reference:=Cells(ActiveCell.Row, 1).Value _
         & "!" & Cells(ActiveCell.Row, 3).Value
End Sub

Another Function, Subroutine listing

  Didn't have a chance to look at this posting 2002-03-08 by Myrna, I think it extracts codes from Add-Ins, if so, could be used to include add-in functions.

	A	B	C	D	E	F	G	H
1	WORKBOOK: LISTSUBS.XLS, 7 Sep 1999
2	Module	ModType	Priv?	Procedure Name	Type	Priv?	Parms?	Line #
3	ListSubsCode	Std		ListProcedures	Sub			44
4	ListSubsCode	Std		GetProcedures	Sub	X	X	161
5	ListSubsCode	Std		ModuleIsPrivate	Function	X	X	282
6	ListSubsCode	Std		ReadTheLine	Sub	X	X	298
7	ListSubsCode	Std		GetProcedureInfo	Sub	X	X	320
8	ListSubsCode	Std		NextWord	Function	X	X	420

References - VBAProject (Tools, References)

List Sheet Names (EnumerateSheets_X95)

Sub EnumerateSheets_XL95()
  'listed from active cell down 2-cols --  DMcRitchie 1999-03-04
  Application.Calculation = xlManual   'xl97 up use xlCalculationManual  
  Application.ScreenUpdating = False
  cRow = ActiveCell.Row
  cCol = ActiveCell.Column
  For csht = 1 To ActiveWorkbook.Sheets.Count  'worksheet or sheets
     Cells(cRow - 1 + csht, cCol) = "'" & Sheets(csht).Name
     Cells(cRow - 1 + csht, cCol + 1) = TypeName(Sheets(csht))
    '-- include next line if you want to see cell A1 for each sheet
     Cells(cRow - 1 + csht, cCol + 2) = Sheets(Sheets(csht).Name).Range("A1").Value
  Next csht
  Application.ScreenUpdating = True
  Application.Calculation = xlAutomatic   'xl97 up use xlCalculationAutomatic
End Sub

List of A1 Cells by Sheetname (GetAllA1Cells)

Sub GetAllA1Cells()
  Application.ScreenUpdating = False  'DMcRitchie 2000-10-24
  Application.Calculation = xlCalculationManual
  Dim iSheet As Integer
  For iSheet = 1 To ActiveWorkbook.Worksheets.Count
    ActiveCell.Offset(iSheet - 1, 0) = Worksheets(iSheet).[a1].Value
    ActiveCell.Offset(iSheet - 1, 1) = "'" & Worksheets(iSheet).Name
  Next iSheet
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Other Cell Hyperlink usages

Create a button on worksheet to invoke a macro

Sub Build_SS_Button()
    'Build button on spreadsheet to invoke macro
    'adapted from a posting by "Jim/Nospam" on 30Aug1999
    ActiveCell.Select
    Selection.Copy
    ActiveSheet.Pictures.Add(251, 88, 75, 13).Select
    Selection.Interior.ColorIndex = 8
    Application.CutCopyMode = False
    Selection.OnAction = ActiveCell.Value   'Macro to be invoked
End Sub

To create a button on toolbar see my page -- Toolbars and Custom Buttons

Using a hyperlink to run a macro

Related Code Snippets

'Show names of sheets in pop-up (same as Rclick on TabNav keys) -- Jim Rech
CommandBars("Workbook tabs").ShowPopup

'Rename current Sheet  -  Tom Oglivy
Application.Dialogs(xlDialogWorkbookName).Show

  If AddIns("Autosave Add-in").Installed = False Then
     AddIns("Autosave Add-in").Installed = True
     MsgBox "autosave add-in reset back to True"
  End If

Working with hyperlinks in Cells

Avoiding taking the hyperlink when selecting a cell

• If you press LtClick but do not release button but a second later, you can select the cell without going to the hyperlink.  You can update your TOC with BuildTOC by selecting in this manner, or selecting a range.
• If you enter into a cell using the arrow keys you will not invoke the hyperlink.
• If you LtClick in a cell and drag within cell before releasing you will avoid going to the hyperlink 
• If you select a range of cells using Ctrl you will avoid going to the hyperlink. 
• If you inadvertently click on a cell with a link and don't want to go there, use the Esc key before or when the Enable/Disable macros nagging dialog appears.

A similar problem is attempting to enter a formula into the formula bar and getting interference from cell addresses popping into the formula caused by backspacing or cell selecting when trying to key in the formula is to press the F2 beforehand.

Function to show hyperlink URL used in another cell

Believe this is working now 2000-02-13 after minor tuneup
Actually the extraction from the hyperlink is just an approximation.

Function URL(cell As Range)
  'Tom Ogilvy, programming 1999-04-14 Deja: AN=468281862
  'Chip Pearson, programming 1999-04-14 Deja: AN=468345917
  'David McRitchie, combined 1999-11-13
  'cannot process imbedded link to internal sheet yet ...
  Application.ScreenUpdating = False
  If Trim(cell.Formula) = "" Then
     URL = ""
     Exit Function
  End If                            ' 1234567890122
  If Left(UCase(cell.Formula), 11) = "=HYPERLINK(" Then
    If Left(UCase(cell.Formula), 12) = "=HYPERLINK(""" Then
      URL = Mid(cell.Formula, 13, InStr(1, cell.Formula, ",") - 13)
      Exit Function  'next part for nonquoted first parm
    End If
    URL = Mid(cell.Formula, 12, InStr(1, cell.Formula, ",") - 12)
    Exit Function
  End If
  URL = ""
  On Error Resume Next
  URL = cell.Hyperlinks(1).Address
  If URL = 0 Then URL = "'**"
  Application.ScreenUpdating = True
End Function

This one works for hyperlinks as hyperlinks not =HYPERLINK(...)
So use the URL function above.

Function HyperlinkAddress(cell)
    On Error Resume Next
    HyperlinkAddress = cell.Hyperlinks(1).Address
    if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

If you change the object hyperlink without reentering it the Function will not show the updated value when changed or when recalculated [F9] until a complete recalculation is performed [Ctrl+Alt+F9]

<a href="http://www.nbc.com/" title="NBC Channel 4 in New York city">NBC</a>

  ActiveSheet.Hyperlinks.Add Anchor:=Range("C22"), Address:= _
     "http://www.nbc.com/", ScreenTip:="NBC Channel 4 in New York city", _
     TextToDisplay:="NBC"

Function HyperlinkScreenTip(cell)
    On Error Resume Next
    HyperlinkScreenTip = cell.Hyperlinks(1).ScreenTip
    If HyperlinkScreenTip = 0 Then HyperlinkScreenTip = ""
End Function		 

Although it would be redundant you could have a subroute to show TextToDisplay.

Manually entering a hyperlink in a Cell to a cell in another sheet

• Ctrl+k or the shortcut globe with chain or View --> Insert --> Hyperlink or RClick choose hyperlink
• Link to: Place in Document (2nd of 4 choices)
• Text to Display: Cell A20 in Sheet2
• Type in the Cell Reference: Sheet2!A20

Removing object hyperlinks from a Cell or cell range

• Select a single cell
• RClick, then Hyperlink
• Remove Hyperlink (on menu, or in lower left corner of dialog box)

The following macro will delete such hyperlinks from the selected area.  It will not delete those you create with =HYPERLINK(..) formula.

    Sub DelHyperLinks()
       Selection.Hyperlinks.Delete
    End Sub

The opposite of this is Creating Hyperlinks for all Cells in a Selection

To select all hyperlinks (not formula hyperlinks) in a selection

   Sub SelectHyperlinks()
    'D.McRitchie 2001-01-24  buildtoc.htm
    Dim hl As Variant
    Dim rng1 As String
    For Each hl In ActiveSheet.Hyperlinks
        rng1 = rng1 & "," & hl.Parent.Address(0, 0)
    Next hl
    If rng1 = "" Then
      MsgBox "No Hyperlinks found in Sheet, so none in selection"
      Exit Sub
    End If
    rng1 = Right(rng1, Len(rng1) - 1)
    On Error Resume Next
    Intersect(Selection, Range(rng1)).Select
    If Err.Description = "" Then Exit Sub
    MsgBox "Change your initial selection" & Chr(10) & _
      "there are hyperlinks, but none in you initial selection" _
      & Chr(10) & Err.number & " " & Err.Description
   End Sub

If you want do delete links (I think selectively) see Find Links by Bill Manville in the MVP area of Steve Bullen's site.  To prevent creation of selected hyperlinks see Q233073 it is an Event macro that will be invoked each time a link is changed. 

Alternative or preventing generation of a hyperlink

Conversion of cell object Hyperlinks to HYPERLINK Worksheet Function

Sub ConvertHyperlinks()
    'David McRitchie, misc, 2000-01-17, misc
    'http://www.mvps.org/dmcritchie/excel/buildtoc.htm
    Dim cell As Range
    Dim hyperlinkaddress As String, hyperlinkformula As String
    For Each cell In Selection
      On Error GoTo skipHyper
      hyperlinkaddress = cell.Hyperlinks(1).Address
      On Error GoTo 0
      If hyperlinkaddress = "" Then GoTo skipHyper
      hyperlinkformula = cell.Formula
      If Left(hyperlinkformula, 1) = "=" Then
        hyperlinkformula = Right(hyperlinkformula, Len(hyperlinkformula) - 1)
      Else
        hyperlinkformula = Replace(hyperlinkformula, """", """""")
        hyperlinkformula = """" & hyperlinkformula & """"
      End If
      cell.Formula = "=HYPERLINK(""" & hyperlinkaddress & _
        """," & hyperlinkformula & ")"
skipHyper:  On Error GoTo 0
    Next cell
    On Error GoTo 0
    Selection.Hyperlinks.Delete
    For Each cell In Selection
      cell.Formula = cell.Formula
    Next cell
End Sub

GoToHTML macro

If you do remove the hyperlinks you could use this method to get to the displayed link, expect this would also work for XL95 but not sure.  Also see GoToHyperlink

Sub GoToHTML()
    'David McRitchie, 2000-12-13
    'documented in http://www.mvps.org/dmcritchie/excel/buildtoc.htm
		'selected cell contains    http://www.abcexample.com
    On Error Resume Next
    If Len(Trim(ActiveCell.Value)) = 0 Then Exit Sub
    ActiveWorkbook.FollowHyperlink Address:=ActiveCell.Value, _
      NewWindow:=False, AddHistory:=True
    If Err.number <> 0 Then
      MsgBox Err.number & " " & Err.Description & Chr(10) & _
      "Tried to bring up file in " & ActiveCell.Address(0, 0) & _
      Chr(10) & ActiveCell.Text
    End If
   'Application.WindowState = xlNormal -- what would this do?
End Sub

The following will take the hyperlink, if it fails you can paste the failed hyperlink into a Find dialog where used.

   Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     Dim MyDataObj As New DataObject
     Dim pLnk As String
     pLnk = Trim(ActiveCell.Value)
     On Error GoTo Failure
     MyDataObj.SetText pLnk
     On Error Resume Next
     MyDataObj.PutInClipboard
   Failure:
     GoToHTML  '-- or GoToHyperlink  (also seen on this page)
     ActiveCell.Interior.ColorIndex = 20
   End Sub

Making the Hypertext the Text only entry in Selected Cells

Sub MakeTextOnlyFromHyperlinks()
  'David McRitchie, 2000-08-23 worksheet.functions !!
  Dim cell As Range
  Dim URL As String
  For Each cell In Selection
    If IsEmpty(cell) Then GoTo chknext
    MsgBox cell.Address
    URL = ""
    On Error Resume Next
    URL = cell.Hyperlinks(1).Address
    If Err.Number = 9 Then GoTo chknext
    If Trim(URL) = "" Then GoTo chknext
    cell.Value = URL
    cell.Hyperlinks(1).Delete
chknext:    On Error GoTo 0
  Next cell
End Sub

Coding a link address and friendly name using HYPERLINK Worksheet function

	A	B	C
1	Site		=GetFormula(Bn)
2	ABC	X	=IF(A2="","",HYPERLINK("http://www." & A2 & ".com","X"))
3	Microsoft	X	=IF(A3="","",HYPERLINK("http://www." & A3 & ".com","X"))
4	NBC	X	=IF(A4="","",HYPERLINK("http://www." & A4 & ".com","X"))
5	NBCi	X	=IF(A5="","",HYPERLINK("http://www." & A5 & ".com","X"))

Besides the obvious advantage of creating Hyperlinks on the fly, is that you can easily change column A without having to use keyboard tricks to avoid taking the link when changing the sites.

The URL formula described on this page will not work on the HYPERLINK formulas shown above.

Worksheet code that created a hyperlink

=HYPERLINK("h:\excel2k\testng2k.xls#sheet3!a1","SHEET3")
=HYPERLINK("[h:\excel2k\testng2k.xls]sheet3!a1","SHEET3")
=HYPERLINK("[d:test\test.xls]test!b5","thisone good b5")
=HYPERLINK("d:\website\dmcritchie","dmcr")
=HYPERLINK("d:test\test.xls","thisone is also good")
=HYPERLINK("[testng2k.xls]'$$TOC'!A56","heidi-ho $$TOC!A56")
=HYPERLINK("[h:\excel2k\testng2k.xls]'$$TOC'!a3","text in a3 in $$TOC")
=HYPERLINK("[file:\\\d:test\test.xls]test!B3","thisone is good")
=HYPERLINK("[http://www.business.com/report/budget report.xls]Annual!F10","Report")
=HYPERLINK("[vlookup.xls]'sheet"&F2&"'!a4","sheet"&F2)   -- using a cell
=HYPERLINK("[vlookup.xls]'sheet"&ROW()+38&"'!a4","sheet"&ROW()+38)  -- cell and sequence

=HYPERLINK("[http://www.mvps.org/dmcritchie/excel/excel.htm]","My Excel Pages")
=HYPERLINK("file:\\\c:\temp\David McRitchie\a.txt","thisone is good")

It appears that even if the link is to a cell in the same worksheet you must include the bookname including .XLS extension, as well as the sheetname.

  =HYPERLINK("[WBName.xls]Michael!A5", "Michael I")

Using HYPERLINK Worksheet Function, where workbook name can change

As aready mentioned you must include the filename, even if it refers to a cell in the same workbook.  You can use this code to generate the workbook name.  As mentioned on my Pathname page you must include the cell reference to get a valid result from the CELL Worksheet Function. 

B3:  =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

and use it as follows:
A1:  =HYPERLINK("["&hyperlink!$B$3 & "]'Sheet One'!A1","Sheet One")

Obtain a hyperlink address and friendly name from linked cell

programming version

See URL function above.  Examine formula instead for those entered as HYPERLINK(...)

        URL = ActiveCell.Hyperlinks(1).Address    'see note on HYPERLINK
        URL_Display = ActiveCell.value

Using the URL function on the Worksheet

Using the User Defined Function to obtain the link address:
  =URL(D4)
  =personal.xls!URL(D4)
Using a Worksheet Function to obtain the text value
  =D4

When using a function that exists in a different workbook, include the workbook name as shown above, failure to find your function will result in an error -- #NAME?

some notes on mail links

In XL2000 if you type an email address (or something that looks like one) you can hit UNDO (ctrl+z) and the link portion will disappear along with the font formatting of a link.

When the link has been established typing into the cell will not affect the link, unless what you type is another hyperlink, which will create the link (in this case replace) in the normal fashion.

I also noticed that while recording a macro that typing in an email address  

Specific Hyperlink usages

Email
    =Hyperlink("mailto:DMcRitchie@msn.com","David McRitchie")

TCP/IP Transmission Control Protocol/Internet Protocol (Internet)
    =Hyperlink("telnet://" & A1,A1)

Creating Hyperlink for a single selected cell

   Sub single_hyp()
      ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:=ActiveCell.Value
   End Sub

Creating Hyperlinks for all Cells in a Selection

  Sub MakeHyperlinks()
     Dim cell As Range
     For Each cell In Selection
       With Worksheets(1)
         .Hyperlinks.Add Anchor:=cell, _
          Address:=cell.Value, _
          ScreenTip:=cell.Value, _
          TextToDisplay:=cell.Value
       End With
    Next cell
  End Sub

Creating Mail Links for all Cells in a Selection

Based on MakeHyperlinks above but created separately.  "mailto:" will be prefixed in creating the hyperlink.

Sub MakeEmailLink()
   Dim cell As Range
   Dim i As Integer
   For Each cell In Selection
     If InStr(1, cell, "@") > 0 Then
         With Worksheets(1)
           .Hyperlinks.Add Anchor:=cell, _
            Address:="mailto:" & cell.Value, _
            ScreenTip:=cell.Value, _
            TextToDisplay:=cell.Value
         End With
     End If
   Next cell
End Sub

For a better solution see MakeHyperLinkFormulas", which creates hyperlinks with the HYPERLINK formulas.

MakeHyperlinkFormulas from URL address or Email addresses

Object type hyperlinks can lead to performance problems.  The following will create HYPERLINK formulas less prone to problems and will combine the functionality of both of the previous macros.  The @-sign will be used to determine email addresses, which is not always a perfect choice, so it comes down to knowing both your data and your macro usage.

 Sub MakeHyperlinkFormulas()
   Dim cell As Range
   Dim hyperlinkaddress As String, hyperlinktext As String
   For Each cell In Selection
       hyperlinkaddress = Trim(cell.Text)
       hyperlinktext = Trim(cell.Text)
       If hyperlinktext = "" Then GoTo skipit
       If hyperlinktext <> "" Then
         If InStr(1, hyperlinkaddress, "@") Then
           If LCase(Left(hyperlinkaddress, 7)) <> "mailto:" Then
              hyperlinkaddress = "mailto:" & hyperlinkaddress
           End If
         Else
          if Instr(1,hyperlinkaddress,".") = 0 then goto skipit
          If LCase(Left(hyperlinkaddress, 7)) <> "http://" Then
            hyperlinkaddress = "http://" & hyperlinkaddress
          End If
      End If
      cell.Formula = "=HYPERLINK(""" & hyperlinkaddress & _
         """,""" & hyperlinktext & """)"
 skipit:
   Next cell
 End Sub

To opposite of this is to delete all hyperlinks in selection, see DelHyperlinks above.

Additional items

• RtClick on a cell to create/modify/remove hyperlinks.
• There is a hyperlink button you can install on toolbar (globe with two chain links).  You can use Ctrl+K to accomplish the same.
• If you change the formatting on a cell that contains a hidden hyperlink you can restore its appearanc by:  Select another cell with hyperlink using slow release, format painter icon, select cell to be fixed using slow release.

Other Things you might want to keep on or near your $$TOC page

1. A list of other Excel files on your system.
2. The last sheet that was modified during sessions use the crippled form =cell("filename"), which indicates the last sheet update; rather than the usually more correct =cell("filename",A1) which gives the information for the sheet where the formula resides.is used.  See HELP, Cell worksheet function; for more information.  The crippled form may be involved with some of my pages that showed through transparently to more recent sheets updated -- see ghosting problems.  Other people have same problem but apparently haven't been able to link any cause.  The ghosting problem can't be caused on demand so it will probably be with us for awhile.

Related Worksheet Functions

• Hyperlink Worksheet Function
• Sub GoToHyperlink()
    Application.Goto Reference:=Range(activecell.value), Scroll:=True
  End Sub

Screen Tips for hyperlink

RtClick on cell --> Edit hyperlink --> [Screen Tip]
   change the screen tip using the [Screen Tip] button,  hit OK (not the [X]
on the way out) both to the screen tip dialog and the hyperlink dialog on
the way out.

The generated code looks something like:
        Selection.Hyperlinks(1).ScreenTip = "ccccc"

With or without a Cross-Reference

Modules marked Option Private Module can be invoked but will not show up in the menu ALT+F8, though they can be typed in and executed using RUN on the menu, or placed wherever a macro can be assigned.

Coding for personal functions (Function) can be located from the VBA editor (Alt+F11) using F2 to get to the Object Browser.  Make sure the first entry "Globals" is selected then look for you functions in boldface next the green boxes interspersed with names and VBA constants.

The [fx] button (Paste Function Wizard dialog box) shows groups of functions including a list ALL functions (near top), the disliked most recently used (at the top), and the User defined functions (near the bottom) of the dialog box on the left-side.  The functions in the selected grouping appear in the right-side window.

Using the [=] button to left of the formula bar shows the most recently used functions.  If you look at bottom it says "Other functions" select that and you get the Paste Function dialog box that you get with the [fx] button.

Converting a list to a list of Hyperlink List

Sub forcelinks()
  Dim xlink As String
  Dim cell As Range
  For Each cell In Selection
    xlink = cell.Value
    If Left(xlink, 1) = "\" Then xlink = "C:" & cell.Value
    cell.Formula = "=HYPERLINK(""" & xlink _
       & """,""" & xlink & """)"
    Next cell
End Sub

preference of using:
    cell.Formula = "=HYPERLINK(""" & xlink _
       & """,""" & xlink & """)"
instead of:
    ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:=xlink, _
            TextToDisplay:=xlink

Simulating a Hyperlink via Macro

    Application.Goto Range("A1000"), True  'Equivalent usage to those below

    'selected cell  contains   Sheet1!Z100
    '  goto indicated sheet and cell, scroll to display cell in top left corner
    Sub GoToHyperlink()
         Application.Goto Reference:=Range(activecell.value), Scroll:=True
    End Sub

    'reposition to address in activecell on sheet named to left of cell
    ' i.e. cell to left of selected cell  contains   Sheet1, selected cell contains Z100
    Sub myHyperlink2()
        Application.Goto Reference:=Range(ActiveCell(1, 0).Value _
          & "!" & ActiveCell.Value), Scroll:=True
    End Sub

Examples:  Linking to a Web Page

   =HYPERLINK("http://www.mvps.org/dmcritchie/excel/excel.htm","My Excel Pages")

    My Excel Pages       in a cell then  RClick --> hyperlink
    http://www.mvps.org/dmcritchie/excel/excel.htm

    Sub Macro3()
       Range("D26").Select
       ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
            "http://www.mvps.org/dmcritchie/excel/excel.htm", _
            TextToDisplay:="My Excel Pages"
    End

Repositioning screen and/or active cell

'repostition active cell to Top Left corner
'   remains on same sheet
Sub ShowTopLeft()
    Application.Goto Reference:=Range(ActiveCell.Address), Scroll:=True
End Sub

'Active cell repositioned to Top also showing 5 cells to left
'   remains on same sheet
Sub ShowTopLeft5()
        Dim caddr As String
    caddr = Selection.Address
    Application.Goto Reference:=Cells(ActiveCell.Row, _
        Application.WorksheetFunction _
        .Max(1, ActiveCell.Column - 5)), Scroll:=True
    Range(caddr).Select
end Sub

Opening a Word Document from Excel

Sub GotoWebsite()
  Dim Link As String
  Link = "http://www.j-walk.com/ss/excel/tips/tip71.htm"
  'address is the origin og this code!
  On Error GoTo NoCanDo
  ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
Exit Sub
NoCanDo:
  MsgBox "Cannot open " & Link
End Sub

before this you have to set reference to Microsoft Word object library in
"Tools > References" in the VB editor:

Sub OpenWordDocument()
  Dim wrdApp As Word.Application
  Set wrdApp = CreateObject("Word.Application")
  wrdApp.Visible = True
  wrdApp.Documents.Open ("C:\Temp\Textfile.txt")
  Set wrdApp = Nothing
End Sub

Navigating within a Sheet

1. Shortcut keys such as lastcell (Ctrl+End), first cell in a row (Home), first cell in sheet (Ctrl+Home).  see keyboard shortcut keys (shortx2k.htm)
2. Name box on left of formula bar allows you to type in a cell or a range to select cell or a range.
3. Shortcut buttons that take you to top of a column, last used cell in a column, last used cell in row.  See Toolbars.htm page.    
4. To scroll fast press Shift key then grab the scroll bar.  Scroll bar will become small and the navigation will be very quick.
5. The wheel on a wheel mouse allows you to move quickly within a few pages.
6. The wheel button itself provides a different kind of navigation that is extremely fast.  The further you move the mouse away from the scroll point the faster you move.
7. Another way that might simplify selection of a range is to the the F8 key toggle which retains the first cell selected as one corner of the range and allows you to reselect the opposite corner until you hit F8 again.

     End of topic: Navigating within a Sheet, begin new navigation topic.

Navigating to Previous, Next, or specific sheet

    ActiveSheet.Previous.Select
    ActiveSheet.Next.Select
    Sheets("Sheet1").Select

One advantage to a web page is that corrections and updates can be made, even though I had checked shortcut keys (XL 95) (XL 2000) and thought there were none for this, I found them later.  I like my short cut buttons to navigate backward or forward through the worksheet tabs but there are two shortcut keys that do the same thing.

    CTRL+PAGE DOWN  Move to the next sheet in the workbook
    CTRL+PAGE UP    Move to the previous sheet in the workbook

A reply post from Myrna Larson to provide for going back to the last viewed sheet <404ftsg4gk02rbi0l2t9hcqj4h5c6sh1f5@4ax.com>

Unsorted Selectable list of Sheetnames

Unsorted selectable list of sheets, RClick on any of the direction arrows to left of sheet TABs, this will pop up an unsorted list of sheets, for a scrollable list goto bottom.  This tip is repeated under Related - Alternatives at end of this web page.

The equivalent in VBA (Jim Rech):  CommandBars("Workbook tabs").ShowPopup

Creating your own menu with all sheets found in Open Workbooks by Workbook, in a solution posted by Chip Pearson. AN=683847198

GoToSheet -- Go to a Specific Sheet

Specify Sheet as in Customer Name to go to

Sub GoToCustomerSheet()
     'David McRitchie  2000-07-15   excel.programming
     Dim WantedSheet As String
     WantedSheet = InputBox("Please Supply Customer Name" _
        & Chr(10) & "This should match a sheetname", _
        "Specify Customer")
     If WantedSheet = "" Then Exit Sub
     On Error Resume Next
     'to goto a cell you still need to slect the sheet first
     Sheets(WantedSheet).Select
     Sheets(WantedSheet).Range("B14").Select  'Optional
     If Err = 9 Then  'actually is subscript out of range
        MsgBox "Your worksheet was not found use RClick on " _
          & "Sheet Tab Navigation arrow in lower left corner " _
          & "to find desired sheetname."
     End If
End Sub

Running a macro for all worksheets in a workbook

If you were interested in the currently grouped sheets you could modify code to use

For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets

 For csht = 1 To ActiveWorkbook.Sheets.Count  'worksheet or sheets
       Cells(cRow - 1 + csht, cCol) = "'" & Sheets(csht).Name

Option Explicit
Sub AllWorkSheets()
    Dim Arr() As String
    Dim I As Integer

    ReDim Arr(Worksheets.Count)
    Arr(0) = Worksheets.Count
    For I = 1 To Worksheets.Count
        Arr(I) = Worksheets(I).Name
    Next I

    For I = 1 To Arr(0)
      Sheets(Arr(I)).Select
      Application.Run "pesonal.xls!MarkCells"
    Next I
End Sub

    Dim sh As Worksheet
    I = ActiveWorkbook.Windows(1).SelectedSheets.Count
    ReDim Arr(0 To I)
    Arr(0) = I
    I = 1
    For Each sh In ActiveWorkbook.Windows(1).SelectedSheets
      Arr(I) = sh.Name
      I = I + 1
    Next sh

more on sheetnames in an array see http://www.cpearson.com/excel/excelM.htm

More notes on HYPERLINKs

Hyperlinks come in two varieties in XL97 and up, and are not available in either variety before XL97.

Embedded hyperlinks that you create by Right clicking on a cell and inserting a hyperlink create an object.

HYPERLINK Worksheet Formula is the other. You can see both in my build.htm page, but mainly of the hyperlink object variety and programmed.

Since Chip Pearson has indicated that embedded hyperlinks can cause problems they add to the number of objects in your workbook, and that the formula hyperlinks do not cause problems, would suggest sticking to the Worsheet Functions variety, which you can include as a formula in programming, and double any internal quotes.
    see F1 (HELP) --> Index --> HYPERLINK Worksheet Function

(see examples on my Worksheet, VBA and Worksheet coding (sheets.htm) page.

Bookmarks, Favorites, My Places, Most Recently Used files

Bookmarks or Favorites into a Spreadsheet

Incidentally you can paste your Internet Explorer Favorites (bookmarks) into an Excel spreadsheet.  File, Export, Favorites. create the (HTML) file, select the HTML which is really the Netscape bookmark format (select ALL), and paste into a worksheet.  But you can read all about that on my pages.  including how to extract the URL from the hyperlinks.  If you have the old Netscape 3.2 you sort the bookmarks and reinstall them as Favorites rather than the absurd limitations that you have in IE and in Netscape also now in order to be compatible, I guess.  If you begin your favorites foldernames with a space and not your webpages, this will work out nicely (sorting with Netscape 3.2).  Update on Bookmarks:  Netscape 6.0 will sort bookmarks temporarily.  Windows 2000 provides sorted bookmarks under favorites whereever seen, including with IE 6.0, also only temporary as an Export will not be an alphabetical order (folders first, then files).  IE 6.0 does not provide sorted bookmarks itself (i.e. not in Win98).

Most Recently Used Files

Excel 2000 provides up to 9

My Places

Q282087 -- OFFXP: How to Customize the My Places Bar in "Open" and "Save As" Dialog Boxes

Remove CRLF characters from ClipBoard

Came up in a discussion in misc group concerning using the notepad to remove CRLF characters when addresses get splitup between lines in newsgroup postings.  This can also be done in VBA, though I doubt that you would actually experience this as much of problem within Excel.  LinkFix simply remove CRLF; whereas, LinkFix_GoTo will remove CRLF and take you to the link.  I have included separate replacement for CR and LF since UNIX machines don't produce CRLF to split lines.  

Sub LinkFix()
' Dana DeLouis 2001-03-19 misc, using an
' Idea From: Chip Pearson
'  http://www.cpearson.com/excel/clipboar.htm
'= = = = = = = = =
' VBA Lib.Ref.: Microsoft Forms 2.0 object lib.
' Excel 2000 due to Replace() Function.
'= = = = = = = = =
  Dim MyDataObj As New DataObject
  MyDataObj.GetFromClipboard
  Dim nLnk As String
  sLnk = Replace(MyDataObj.GetText, _
     vbCr, vbNullString)
  sLnk = Replace(sLnk, vbLf, vbNullString)
  sLnk = Replace(sLnk, vbLf, vbNullString)
  sLnk = Replace(sLnk, ">", vbNullString)
  sLnk = Replace(sLnk, " ", vbNullString)
  sLnk = Replace(sLnk, Chr(160), vbNullString) 
  MyDataObj.SetText sLnk
  MyDataObj.PutInClipboard
  Set MyDataObj = Nothing
End Sub

Sub LinkFix_GoTo()
' Dana DeLouis 2001-03-19 misc,
'  Dave Peterson, added FollowHyperlink
' Idea From: Chip Pearson
'   www.cpearson.com/excel/clipboar.htm
'= = = = = = = = =
' VBA Library Reference:
'    Microsoft Forms 2.0 object lib.
' Excel 2000 due to Replace() Function.
'= = = = = = = = =
  Dim MyDataObj As New DataObject
  Dim lnk As String
  On Error Resume Next
  MyDataObj.GetFromClipboard
  lnk = Replace(MyDataObj.GetText, _
     vbCrLf, vbNullString)
  Set MyDataObj = Nothing
  ActiveWorkbook.FollowHyperlink _
    Address:=lnk, NewWindow:=True
End Sub

Special Note on Compatibility

Replace was not available in Excel 97 so for XL97 you would have to use Excel Substitute instead of VBA Replace.  i.e.

   ' qSht = Replace(Sheets(cSht).Name, """", """""") -- replace not in XL97
   qSht = Application.Substitute(Sheets(cSht).Name, """", """""")

Related - Alternatives

Sorted selectable list of Subroutines, Tools --> Macros; or Alt+F8, allows running or Editing of subroutine.

Sorted nonselectable list of Subroutines & dialogs, File --> Properties --> contents

Sorted list of Functions, use function wizard or paste function wizard [fx] --> User Function,   shows syntax, can show help if available, but does not provide a path to view code.

Populating a ListBox with Sheetnames: AN=582002850
to select:  also see Tom Ogilvy Feb 13 2000 in programming *******

Related - Menus

My own major categories are:  selected high use individual macros, followed by categories of macros:  Create Test Data, Documentation (footings), Documentation Set, HTML creation, Information, Rearrange Data on Another Sheet, Reformat
Also note the alphabetical arrangement.

Additional Menu Links

Also see Toolbars, Custom Buttons and Menus.  Some of these topics are inter-related.

Q141688 -- XL: Visual Basic Macros to Add or Delete a Custom Menu

Q159619 -- XL97: Sample Macros to Control Menus and Submenus

Q166755 -- XL97: WE1183: "Customizing Menu Bars, Menus, and Menu Items"

http://home.telia.no/exceltips/

For macros containing examples (Excel5/95 and Excel97 and later) on how to create and modify menus.  Look in VBA part 2 CommandBars. -- Ole P. Erlandsen

Command and Menu Bars -- Charlie Kindschi, Microsoft Corporation

DP2586 -- Customizing Menu Bars, Menus, and Menu Items in Microsoft(R)

Menus and Command Bars , Jon Peltier

Coding in Menus

    Dim mnbNew As Menu

    Set mnbNew = MenuBars(xlWorksheet).Menus.Add("&Options")
    mnbNew.MenuItems.Add "Run &Utility", "Show_Form"
    mnbNew.MenuItems.Add "-"

Related