How To Call It

**Launchnet** · 05-27-2008, 11:18 PM

I have a "Menu" workbook that has 1 sheet named "MyMenu" which only has macros and Hyperlinks on it.

I would like to add a second sheet to this workbook named "SetUp".

Next, as an example, I would have the user list 20 Excel Workbook.xls files on the 2nd sheet (SetUp sheet) in Cells A1 thru A20 (as my example). In column B, directly adjacent to each file, they would enter a generic name of their choice, representing each file. These are files that they would be using frequently. The file names would be entered in cells B1 thru B20.

Now, I need 20 macros on "MyMenu" that can open these appropriate files listed on the SetUp sheet. As an example, I could have 20 macros that are located in Column D on "MyMenu". They could be located in cells D5 thru D24.

2 Things I want these macros to do . . .
First . . .
The Generic name would be displayed in the appropriate cell in column D so that the user would know which workbook they are going to open.

And Second . . .
Clicking on the Generic name would open the associated file.

NOTE: If the user were to enter a new file name in one of the cells in column A and a new Generic name, the name for the macro on "MyMenu" would automatically be changed and the new file name would be opened when the macro was clicked.

RE-STATED:
What I am looking for is a way of running macros from "MyMenu" sheet by clicking on any paticular macro I want to run just like normal . . . but, I want the macro on "MyMenu" to open the file that is listed in column A of the SetUp sheet.

REASON:
"MyMenu" has to be a locked sheet. Therefore, the user can not enter or change macros on "MyMenu". I actually have 5 or 6 macros that I would like them to be able to change, but again, "MyMenu" has to be "Password" protected. Additionally, many of the users simply don't know anything about macros or hyperlinks

Anyone have any ideas ?

I would certainly appreciate any help and suggestions.

**rylo** · 05-28-2008, 12:49 AM

Hi

Setup!A1 has the full path of the file you want to open
Setup!B1 has the generic name
MyMenu!D1 had the formula =Setup!B1
Right click on the sheet tab for mymenu, select view code and insert the code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("D1:D20")) Is Nothing Then
    Workbooks.Open Filename:=Sheets("setup").Range("A" & Target.Row).Value
  End If
    
End Sub

Now if double click on mymenu!D1 it should open the nominated workbook.

HTH

rylo

**Launchnet** · 05-29-2008, 12:39 AM

Hi Rylo

Excellant. Does exactly as I want. Good Job !!!!!

On "MyMenui" I have a good number of other macros and hyperlinks which all work with ( 1 ) click only.

I would appreciate it very much if the activation of the macro (you have coded) could be activated with only ( 1 ) click. Sorry, but I am so used to using ( 1 ) click for everything that I failed to mention it in my description.

Please let me know.

p.s. I will be posting another question after I hear from you.

Thanks

Matt @ Launchnet

**rylo** · 05-29-2008, 02:06 AM

Hi

Don't believe there is an event for single click. However, you could try putting it to a selection_change event and see if that would work for you. Trouble is that you have to make sure of your cell selection. If you get it wrong, then the macro would fire.

rylo

**Launchnet** · 05-30-2008, 11:30 AM

Hi Rylo . . .
Your 1st suggestion works very good, but I have decided that a mixture of 6 to 8 double click menu items and the balance of 25 to 30 single click items isn't good. Whatever I use, it has to be single click.

I don't think that the second suggestion would work.

Last night I was looking in VB Help and found the "OnActionProperty"

Under example it has:
This example causes Microsoft Excel to run the ShapeClick procedure whenever shape one is clicked.

Worksheets(1).Shapes(1).OnAction = "ShapeClick"

To my limited code experience, it looks like this could possibly work.

I could have a transparent shape on top of cell holding =setup!B1
Same for each of the other links.

Could this work? I don't understand Worksheets(1) and Shapes(1) Does the (1) get replaced with the name of the worksheet, such as: MyMenu and the Shape name?

Maybe there is some other event that could work ?

Any advise and help would be appreciated.

Thanks

Matt @ Launchnet

**rylo** · 05-30-2008, 06:48 PM

Matt

Yep, that may work. Hadn't thought of a transparent shape.

The 1 is the index number of the item. Worksheets(1) is the first worksheet in the workbook, listed from left to right. So it doesn't matter what the name of the sheet is, it will always take the leftmost item.

Same sort of thing for the shape only I'm not sure of the ordering. Put some shapes on a sheet, mix them up and see how they are selected when incrementing the index number.

rylo