Variable menu with single "on-action" procedure

[jo77]

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.

 

[Bernie Deitrick]

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("SheetName").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).Value
.TooltipText = Worksheets("SheetName").Range("G:G").Cells(k).Value & "\" & 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


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.

 

[Chip Pearson]

In each CommandBarButton that is to link to a file, store the full
file name (including the path) in the Parameter property of the
control. Then, assign the same OnAction procedure to each control. In
that procedure, use ActionControl to get a reference to the control
that was clicked and get the Parameter of that control. Then, do
whatever it is you need to do with the file name.

Sub OnClick()
Dim Ctrl As Office.CommandBarControl
Dim FileName As String
On Error Resume Next
Set Ctrl = Application.CommandBars.ActionControl
If Ctrl Is Nothing Then
Exit Sub
End If
FileName = Ctrl.Parameter
' do something with FileName
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[jo77]

Hi Bernie & Chip,
Thanks a lot for your help. I tried it this morning (it's morning
here) and is working fine.
A nice day to both of you.
Joel
PS: I tried to send a message to both of you, but every time, I get a
technical error....

 

[Bernie Deitrick]

Joel,

Glad to hear it. , and thanks for lettingus know.

Bernie
MS Excel MVP

P.S. My email address in the "reply to" is scrambled up a little to cut down on spam.