Solved

Excel 2003 The macro cannot be found

Posted on 2011-03-01
13
1,575 Views
Last Modified: 2012-08-14
I create a custom button in an Excel workbook and assign a macro to it.  When I copy the workbook to a different folder, open it, and click on the button, the error "The macro <full original path>\my_workbook.xls!my_macro" cannot be found" appears. Why is it looking for the macro in the original location where the workbook was created?  How can I fix the problem?  I need to distribute this to various locations.  Thanks.
0
Comment
Question by:glentek
  • 6
  • 5
  • 2
13 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
can you attach the file, or at least your commandbutton macro script?

Also, try this.  Delete the command button, re-created it.  in design mode double click on it, then do the CALL to your macro.  I assume your macro is in the same workbook and you're using CALL to invoke it, correct?

Save and test - did that help?

Can you upload your code and/or file (or mock file that has the same problem)?

Dave
0
 

Author Comment

by:glentek
Comment Utility
I think I figured more details about the problem.  I created the custom button on a custom toolbar and assigned a macro to the button.  I attached the custom toolbar to the workbook, saved the workbook, closed it, then copied it to a different folder.  The custom toolbar and button remained on the Excel toolbar.  I opened the copy.  The custom button is left over from the original and points back to the original, even though the copy is now open.  Opening the copy does not overwrite the custom toolbar and custom button.

I find that if I close the original, delete the custom toolbar from Excel, and then open the copy that is in a different folder, the custom toolbar and custom button from the copy populate the menu area, and the custom button successfully launches the macro.

It seems I need to write some code that deletes the custom toolbar from Excel when the workbook closes, so that the attached custom toolbar for a workbook is used instead of a leftover custom toolbar.

So now the questioin is what is the VBA code that will do it, and how can it be called when a workbook is closed.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I have something that might help - see attached, and also this post:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26837274.html

This should give you code to populate the menu bar as well as to delete it.

Dave
CommandBars-r1.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
This version sets the cDebug flag to TRUE.  It should only be set to true if this package is not part of an add-in.  Performance may be better with this...

Dave
CommandBars-r2.xls
0
 

Author Comment

by:glentek
Comment Utility
@Idlmille I'll take a look at your proposed solutions tomorrow.

It seems odd that Excel would keep a custom toolbar that is attached to a workbook after the workbook is closed.  Intuitively I would expect the custom toolbar to disappear when the workbook is closed, because the custom toolbar is attached to that specific workbook.  Isn't there a way to configure Excel to do (what I consider to be) the "right" thing?
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
If you close out Excel, the custom menu goes away, correct?  There must be code to handle with the worksheet/addin that uses the custom menu is closed.  I spent quite a bit of time hunting around (no manuals messing up my floor, lol) the internet and pieced this together and seems pretty solid.

If you hang it on the Addin's toolbar, its there to stay until you close Excel, unless you do something about it.  Perhaps its kind of like writing to another workbook's sheet - that stuff doesn't dissappear either when you close the writing file down...

I think you'll find your solution in what I sent you - feel free to post any questions and Good Luck!

Dave
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 50 total points
Comment Utility
(Not for points - just explanation)
If you attach a toolbar to a workbook, it gets added to the toolbars on any machine that opens the workbook. It is not, sadly, specific to the workbook - in other words, it is not removed when the workbook is closed, or deactivated when you switch to another workbook.
You are much better off using code to create and hide/show your toolbars as Dave has suggested.
0
 

Author Comment

by:glentek
Comment Utility
@dlmille No. The custom toolbar does not go away.   rorya has it right.  The attached custom toolbar stays there forever, no matter what workbook is opened afterward.  Even if another workbook is opened that has an attached custom toolbar of the same name,  it does not overwrite the original.  So if a custom button activating a macro had been placed on the original custom toolbar, that button will always open the original workbook so that it can run the macro.  If a copy of the original workbook (which would have attached custom toolbar and custom button) is opened, and that copy has the the same file name as the original but it is located in a different folder, the error that I first stated will occur.  I believe it actually is because Excel can't open the original to run the macro because it can't open 2 workbooks of the same name, even if they are in different folders.  If the original and the copy have different file names, the error does not occur, but the custom button will always open the original workbook to run the macro, even if it is the copy workbook that has been opened by the user.

@rorya I agree that the way this works is sad.  A custom toolbar that is attached to a workbook should be for that workbook only.  What occurs now is very non-intuitive.

Can I avoid having to generate the custom toolbar from code? Is there no way I can just write some code to delete the custom toolbar when the workbook is closed or on exit?  
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Re your last Q, no, because of your original problem. The buttons will point at the original location. It is much better to use code to create and destroy the toolbar. It's also not that hard, especially as you already have sample code provided (though I confess, I haven't looked at it - I just assume it works. :))
0
 

Accepted Solution

by:
glentek earned 0 total points
Comment Utility
@rorya  Not trying to be stubborn (it just comes naturally).  It seemed to me that if a custom toolbar (and associated custom button) have been added to Excel, that there should be a way via VBA code to delete it from Excel even though it was not added to Excel via VBA code.  And here is how to do it.  In the Workbook object of ThisWorkbook in the VBE VBAProject, add the following subroutine.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("My Custom Toolbar").Delete
End Sub

Boom.  Done.  That's all I needed.  On close of the workbook, the toolbar is deleted.  When the workbook is reopened, the attached custom toolbar appears again.  Close - delete.  Open - reappear...

If I copy the workbook to another location and open it, the custom toolbar has already been deleted, so the attached custom toolbar is created from this copied workbook, and it's button correctly launches the macro stored with that copied workbook.
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 450 total points
Comment Utility
@glentek - I knew it didn't go away - that's what I was saying - unless you do something about it.

I gave you code that deletes the command bar based on a couple events, including Workbook_Beforeclose -  does exactly that, re: my commandBars routine, a couple posts back.

In the attached I sent, is the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call MyGreatMacro_Terminate
End Sub

and

Public Sub MyGreatMacro_Terminate()
    On Error Resume Next 'if exit commandbar menu, then this fires, then workbook_BeforeClose fires, so would get an error the second time around
   
    CommandBarMenu1.Delete
   
    If cDebug Then
        Application.EnableEvents = False
        ActiveWorkbook.Close
        Application.EnableEvents = True
    Else
        AddIns(addInName).Installed = False
        ThisWorkbook.Close SaveChanges:=False
    End If
   
    On Error GoTo 0
End Sub

Done, yes!

Good,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
PS - commandbar1 is a public variable set to the commandbar, when the bar is created.  you can also reference with the name, as you have done.

Glad you worked it out.

Cheers,

Dave
0
 

Author Closing Comment

by:glentek
Comment Utility
I gave dlmille 450 points because he provided a path that probably would work, although I was looking for a simpler solution.  I came upon the simpler solution so I chose my solution as "the" solution.  I gave rorya because of his comment that it is sad that a custom toolbar attached to a workbook has a life of its own, even when the workbook is closed.  I agree that it is sad and certainly non-intuitive.  But then Microsoft has done worse things with MS Office (such as Office 2007 and the freaking "ribbon" productivity killer - A class action lawsuit should be made against Microsoft for that debacle - are you listening Steve Balmer or Bill Gates?  I didn't think so.).
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now