How To Call It

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.

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

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.

Please Login or Register  to view this content.

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

Hi Rylo

I can make my procedure run by clicking on the shape as it is directly connected to the procedure. Problem is that I need a different procedure for each macro and the name of each file has to be hard coded into each macro, which can not be changed after the password is set, therefore it doesn't do what I need.

Re your double click code, I don't know where to place this private (?) code - or - how to call it

Can you look again and see if there is a way of running your code, only clicking on the shape one time ? Also where to place the code and how to call it.

I think that it should work, but I'm lost.

Your help would be appreciated.

I have a macro that will go to a particular cell that holds the Path & file name to be opened. It then opens that file in a new instance of Excel. Works very good, but Path & cell has to be on the then currently displayed page so that when the operator goes back to the current page, the same data is displayed.

I can make my problem work with the Path & file name located off to the right far enough that it does not show on large screens. I tried the BA column. It works good, and opens the file, but . . .

When the file is closed, I want MyMenu screen to be shown starting with home position of A1. EX: Like doing a "CTRL HOME".

Question:
When I close the sheet that I opened, is there a way of returning the cursor to A1 on MyMenu? All my sheets are closed with a macro in the upper left corner of the sheet. When the sheet is closed, the macro is also closed and therefore I can't continue on to do the "Ctrl Home" function.

Is there a way of doing "CTRL HOME" whenever the workbook is re-activated?

Any ideas ?

Thanks
Matt@Launchnet

I checked your code and the single click works perfect. Then, I realized that I needed to do some checking so I have been working with a different macro that I had help with and I have everything working except at the very end of the code. Can you give me the correct code to have cell A1 selected on the newly displayed workbook? None of the 3 rows of code at the bottom will do anything. I think it is because the new workbook is not activated, although it is open.

I want to thank you very much, as I have learned alot through this process. You've really been great.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

'oXL is needed to open a new instance of Excel.
'Without it, the file is only opened as a new Window
'and I need all opened in a new instance of Excel.

Please Login or Register  to view this content.

'The following tests for a blank cell and ends processing

Please Login or Register  to view this content.

'The following tests for the existance of the file
'If the path or file is wrong, the processing ends.

Please Login or Register  to view this content.

'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL.

Please Login or Register  to view this content.

'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.

Please Login or Register  to view this content.

'This opens the file in the variable . . . SetUpPathCell

Please Login or Register  to view this content.

'I'VE TRIED TO USE THE FOLLOWING 3 ROWS OF CODE, I HAVE TRIED TO ACTIVATE THE FILE AND THEN SELECT CELL A1.
'NATURALLY, I CAN'T MAKE IT WORK.
'SetUpPathCell.Activate
'Application.Goto Reference:="R1C1"
'Range("A1").Select

Please Login or Register  to view this content.

Thanks Again
Matt @ Launchnet

p.s. When I get this resolved, I will post the next similar question, but different. Just to let you know, I'm 76 so I have a good excuse for learning slow.

Hi Rylo

Thanks . . . I used your last line and added it to the last procedure I sent you. It works great.

ADDITIONALLY, When I close the file that I have opened (which is in a new instance of Excel), I would like (after closing) to have the displayed file activated and the Cell A1 selected.

I don't know if it is possible since both files are in separate instances of Excel.

I just now thought of a way of always having the Selection set at A1 on the current file. When the opening of a new file procedure is called, I will first select Cell A1, then finish the opening of the new file.

That still leaves the original file not activated when the new file is closed.

My closing code is as follows: It closes the active file.

Sub GoToClose()
Application.Goto Reference:="R5C2"
Application.Goto Reference:="R4C2"
Application.Goto Reference:="R1C1"

Application.Quit
End Sub

The reason for the 3 rows of Applicatio.GoTo is because of the use of Freeze Panes. This method brings everything below the Freeze line up to the top and then goes to A1 for the selection

Now that we have closed the file we opened, the displayed file is MyMenu and it is not active yet or selected whatever.

Thanks for all the help !!!!!

Matt @ Launchnet

Hi Rylo

Please Login or Register  to view this content.

It works to here . . . The file that I opened shows the cell selection that I had selected when I originally saved the file. I click on another Cell and it moves to that cell, so I believe that the workbook is active.

But the following code will not move the selection. I even tried: Application.Goto Reference:="R1C1" this won't move it either.

The following line is what I want to work, but it doesn't.

Please Login or Register  to view this content.

Since we are dealing with 2 seperate instances of Excel, I think that I will add a line of code (Application.Goto Reference:="R1C1" ) to each closing procedure so that when any file is opened, the selection will automatically be A1. It appears that this will work except for activating which ever workbook is open when we close a different one.


The balance of your code closes the newly opened workbook and I do not want to close it at this point. The procedure is simply to open the file and have Cell A1 selected as described above. VERY SORRY if my previous explainations were not clear.

The following code would be nice if it worked with my closing code, which it doesn't. When the active workbook is closed with my Closing Procedure, I want the original (starting) workbook that opened this workbook to be activated and Cell A1 be selected.

Please Login or Register  to view this content.

Thanks Again

Matt

Hi

No, I don't have a sheet 1.

I am going to have all the main pages in each workbook named Page One . . . I think. Especially if it will help solve the activate and selecting Cell A1

Matt

Here I am again

The opening page to the menu has two sheets and the main page name is: MyMenu and the second sheet is: SetUp

These names will have to stay as is and the workbook is protected.

Matt

I tried this earlier and it wouldn't work.

Let me play with it some more and I'll let you know about my outcome.

I've learned alot plus you've helped with my problems. I'm just working on making the Menu System Smoother and Easier and Adjustable to a certain extent.

Thanks Very Much

Matt

p.s. I'll send you a posting notice via email after I post the next question. It sorta falls in the same vane as where you helped me open a workbook from the setup page. This time it will be using URL's.

I understand that I need to post again for different program so I'll let you know.

I really have enjoyed working with your knowledge. I wish I had it.