Joel,

Below is some sample code that may help you, which uses the tooltiptext of the commandbarbutton to
store the links.

Run CreateCommandbar in the workbook open event, and DeleteCommandbar in the workbook beforeclose
event.

Change Worksheets("SheetName") to whatever the sheet name is, and the module name in this line

..OnAction = ThisWorkbook.Name & "!" & "Module1" & ".WhichButton"

I have also assumed that your list starts in row 2, with a header cell in row 1.


HTH,
Bernie
MS Excel MVP


Option Explicit
Dim myBar As CommandBar
Dim myButton As CommandBarButton
Dim myFileName As String
Dim i As Integer
Dim k As Integer

Sub CreateCommandbar()

'Get rid of the commandbar, if it exists
DeleteCommandbar

'Start creating the commandbar
Set myBar = Application.CommandBars.Add(Name:="Accounting")

'Create the link buttons from stored data
i = Application.WorksheetFunction.CountA(Worksheets("S heetName").Range("G:G"))
If i 1 Then
For k = 2 To i
Set myButton = myBar.Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
myFileName = Worksheets("SheetName").Range("J:J").Cells(k).Valu e
.TooltipText = Worksheets("SheetName").Range("G:G").Cells(k).Valu e & "\" & myFileName
.Caption = myFileName
'All buttons have the same .OnAction
.OnAction = ThisWorkbook.Name & "!" & "Module1" & ".WhichButton"
.FaceId = 23
.BeginGroup = True
End With
Next k
End If
End Sub

Sub DeleteCommandbar()
On Error Resume Next
Application.CommandBars("Accounting").Delete
End Sub

Sub WhichButton()
'This is the macro called by all the link buttons
ActiveWorkbook.FollowHyperlink Address:=CommandBars.ActionControl.TooltipText
End Sub


"jo77" wrote in message
...
Hi all,

I have created an Excel Menu (CommandBars, Type:=msoControlPopup)
(using Excel 2003). Let's call this menu: "Accounting". The menu
"Accounting" contains a sub-menu item, called "User Manuals".
When a user starts Excel, a module lists all files in a given path,
let's say C:\Accounting\UserManuals\ , as well the files of each sub-
directory of this path.
Each file found is put on a worksheet with following information:

Column G: Path which has been searched
Column H: Length of the path name
Column I: Hyperlink to the file
Column J: Name of the file (without path)

Then:
For each file found within C:\Accounting\UserManuals\ , a module adds
the name of the file to the sub-menu "User Manuals" (using a for…each
loop)
For each sub-directory within C:\Accounting\UserManuals\, the module
creates a sub-menu item within "User Manuals", and then adds the name
of each file found within the sub-directory to it (again using a
for...each loop).

So at the end, the Accounting menu could look like this:

Accounting
Balance Sheet - opens an Excel template, mentionned here just as
example
Currency Rates - opens an Excel template, mentionned here just as
example
User Manuals
Manual for currency rates.pdf - file located in C:\Accounting
\UserManuals\
Manual for xyz.pdf - file located in C:\Accounting\UserManuals\
Balance Sheet - sub-directory in C:\Accounting\UserManuals\
Manual for balance sheet.pdf - file in C:\Accounting\UserManuals
\Balance Sheet\
Manual for abc.pdf - file in C:\Accounting\UserManuals\Balance
Sheet\
Booking Manuals - sub-directory in C:\Accounting\UserManuals\
Manual for trades.pdf
Manual for dividends.pdf

Now my problem: I would like to write a single module to call, so that
when a user selects any of the menu items within "User Manuals" or
within a sub-menu of "User Manuals", I can activate the corresponding
hyperlink of the file stored on the worksheet.
Can anyone help or suggest me something?

I hope my explanation is clear (not really easy to explain), I'm quite
new in the VBA world and even less familiar with the menu procedures.
Thanks in advance for any help.