MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Worksheet can't find macro

Posted by Mark on September 18, 2000 1:30 AM

I'm trying to run a macro for copying cells on my sheet and it's not working. Can I not save the sheet as another name or does that not have anything to do with it. I have to keep reassigning the macro to a button if I ever save the sheet under another name even though its the same sheet still. What's wrong?

Posted by Rees on September 19, 0100 12:08 AM

I don't know if you got your answer yet, and I don't know what the personal macro file is either, though I think I may have heard of it before. I believe what you need to do is to use VBA to link your buttons to macros at start up time. If you still need help with this then post a message saying so and I will try to give you more details.


Posted by DoughBoy on September 19, 0100 1:09 AM

try this.

Try recording one more macro.
where it says store macro in choose personal macro workbook.

It doesn't matter what you record we will delete it in a minute anyways. After you stop recording go to the vba editor alt f11 , I think.

Anyway in the project explorer you now have a file called personal.xls

Erase the code you just recorder and move your 7 modules in there and link up the buttons.

****Note**** This will only work for the computer you are working on. If you move the files to another computer you are SOL.

If you don't follow give another shout.

Posted by DoughBoy on September 18, 0100 3:48 AM

How are you calling it? When you are calling a macro from the sheet and copy the sheet it links the button back to the original book.

If you copy the book this won't happen.

If this is just "your" macro try saving it to your personal macro file.

Posted by Mark on September 18, 0100 10:36 AM

I have a bunch of different Macros in 7 modules that I can start with different buttons I put on the sheet. If I save the workbook as another name which I do alot, when I try to run any of the macros after the save, it tries to open the original template workbook. How stop this problem, so that a button will try to open "macro1" on "workbook1" instead of trying to open "workbooktemplate.xls!macro1" for example after I save? If I need to save a macro to be in the personal macros, how do I convert all 7 modules that I have done already? Where is "my personal macro file"? Thanks.

Posted by Mark on September 19, 0100 12:53 PM

Yeah, the code for that would be helpful Rees. If I do that then I would be able to use the worksheet on any computer right? The Personal.xls thing worked, thanks Doughboy, but I would like to be able to take this file home and work on it to. Also, I have a custom toolbar attached to the file that comes up when I work on my computer at work, but when I open it at home, it can't find the custom toolbar. This custom toolbar actually has buttons for all of my macros, except workbook_open. I have in workbook open:

Application.CommandBars("Custom Toolbar1").Visible = True

but it only shows up on my work computer, where I created it. There's an error in finding the toolbar when I open the file from home. Is this along the same line? Thanks for the help.

Posted by Mark on September 19, 0100 1:01 PM

About the toolbar question

Ok, figured out how to attach the toolbar by going to the toolbar properties. Still would like the info about how to attach the macros to the sheet with VBA. Thanks.