MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Link macro to workbook on open?


Posted by Danny on September 26, 2000 9:21 AM

How do I link all of the macros in a workbook to the
workbook itself on open in case of a file name change
where the macros try to open previously named workbook?

Thanks


Posted by Kathy Truitt on September 26, 2000 1:28 PM

I have a workbook that has automatic links to three files that no longer exist. How do I delete the name of the files that no longer exist or would I have to use the same solution as this original question and link to itself?

Posted by Celia on September 26, 2000 4:48 PM

Danny
Instead of using the workbook name in the macros, use code such as ActiveWorkbook or ThisWorkbook.
Look at the following for an example :-
4502.html
Celia

Posted by Celia on September 26, 2000 4:53 PM


Kathy
Check whether the followimg is what you need :-
4502.html
Celia

Posted by Danny on September 27, 2000 9:40 AM

Doesn't work for me...

Celia, I understand what the other person was requesting, and in fact my request is different. They were trying to find a workbook after it was renamed "after the macro was started." My problem is that I can't even start the macros with buttons I've assigned them to because the template workbook itself is being renamed. I use my template once or twice a week and save it as the date, so I have an additional workbook and the template still in tact. The problem is that if I open the newly named workbook and push one of the buttons which were assigned to the template.xls!macro1 for example, it tries to open template1.xls. I've tried putting all macros in the 'This Workbook' section in vb editor, but that doesn't work. I need a way for each macro to be run from it's own workbook, no matter what the name of the file is. If I use the template today and rename it 9-26-00.xls, I need to be able to run 9-26-00.xls!macro1 not template.xls!macro1. Any ideas? Thanks for all of your help.

Danny

Posted by Celia on September 27, 2000 5:09 PM

Re: Doesn't work for me...

Danny
Use buttons from the Control Toolbox instead of from the Forms toolbar.
Attach your code to the button by putting it in the button's code module as "Private Sub CommandButton1_Click()".
Celia

, I understand what the other person was requesting, and in fact my request is different. They were trying to find a workbook after it was renamed "after the macro was started." My problem is that I can't even start the macros with buttons I've assigned them to because the template workbook itself is being renamed. I use my template once or twice a week and save it as the date, so I have an additional workbook and the template still in tact. The problem is that if I open the newly named workbook and push one of the buttons which were assigned to the template.xls!macro1 for example, it tries to open template1.xls. I've tried putting all macros in the 'This Workbook' section in vb editor, but that doesn't work. I need a way for each macro to be run from it's own workbook, no matter what the name of the file is. If I use the template today and rename it 9-26-00.xls, I need to be able to run 9-26-00.xls!macro1 not template.xls!macro1. Any ideas? Thanks for all of your help.

Posted by Danny on September 28, 2000 9:44 AM

Re: Doesn't work for me...

Ok, I figured that out now thanks, and it works fine for putting command buttons on the sheet itself. But what I'm really trying to do is make a toolbar, or some arrangement of floating buttons that can be accessed from any of the 6 or 7 sheets in the workbook. I've tried adding a command button to my toolbar, but it acts the same way as my custom button (where I have to assign a macro through the macro dialog box instead of vba). Is there a way I can create a cluster of command buttons instead of the forms toolbar buttons and have them on a toolbar or floating over all sheets? My only other option is to have a sort of control panel that I must return to every time I want to run one of these macros, since I'm not really big on accelerator keys. Thanks again.

Posted by Celia on September 28, 2000 5:08 PM

Re: Doesn't work for me...

Danny
Try this :-
Put your macros in the file called Personal.xls
which you should find in the XLStart folder (alternatively, put them in some other file that contains nothing except the macros and put the file in XLStart).
The file will be opened autuomatically when you open Excel. Keep the file hidden.
The macros in the file can then be called by toolbar buttons or from macros un other files.
Celia


Posted by Danny on September 28, 2000 5:40 PM

Re: Doesn't work for me...

I've already thought of and tried this. The problem is that I need to be able to transfer this workbook from computer to computer and be able to have a stand alone program. If I put the macros in personal.xls, they only work on one computer. Are there any other options for me? Is this one of the things Microsoft didn't think about? Thanks again

: Ok, I figured that out now thanks, and it works fine for putting command buttons on the sheet itself. But what I'm really trying to do is make a toolbar, or some arrangement of floating buttons that can be accessed from any of the 6 or 7 sheets in the workbook. I've tried adding a command button to my toolbar, but it acts the same way as my custom button (where I have to assign a macro through the macro dialog box instead of vba). Is there a way I can create a cluster of command buttons instead of the forms toolbar buttons and have them on a toolbar or floating over all sheets? My only other option is to have a sort of control panel that I must return to every time I want to run one of these macros, since I'm not really big on accelerator keys. Thanks again.

Posted by Celia on September 28, 2000 8:04 PM

Doesn't work - next episode

Danny
What! Are you suggesting that there may be a possibility of MS not having thought of everything?
If you don't also want to transfer the file with the macros to each computer's XLStart folder, then have you thought of doing an add-in?
This also has the advantage of being available to many network users rather than only one user at a time in the case of a workbook.
However, would not recommend an add-in if you frequently need to amend the macros or add new ones. In such a case, would probably be easier to put the macro-containing workbook in each conputer's XLStart.
Celia

I've already thought of and tried this. The problem is that I need to be able to transfer this workbook from computer to computer and be able to have a stand alone program. If I put the macros in personal.xls, they only work on one computer. Are there any other options for me? Is this one of the things Microsoft didn't think about? Thanks again : Try this :- : Put your macros in the file called Personal.xls : which you should find in the XLStart folder (alternatively, put them in some other file that contains nothing except the macros and put the file in XLStart). : The file will be opened autuomatically when you open Excel. Keep the file hidden. : The macros in the file can then be called by toolbar buttons or from macros un other files. :

Posted by Ivan Moala on September 29, 2000 2:15 AM

Re: Doesn't work for me...

What you are probably after is to create a toolbar
@ startup to do this you will need to code it in.
Coding for this can be arduous....better if I send
you an example.....If you want ???


Ivan