Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable menu with single "on-action" procedure
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable menu with single "on-action" procedure
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable menu with single "on-action" procedure
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) On Mon, 16 Mar 2009 08:17:25 -0700 (PDT), jo77 wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable menu with single "on-action" procedure
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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable menu with single "on-action" procedure
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. "jo77" wrote in message ... 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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
Multiple "source" workbooks linked to single "destination" workboo | Excel Worksheet Functions | |||
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F | Excel Discussion (Misc queries) | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
Menu Item Action Changes with "User" accessing file | Excel Programming |