Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

"Cannot run the macro ...The macro may not be available in the workbook or all marcros may be disabled."

Posted on 2014-07-14
9
Medium Priority
?
25,214 Views
Last Modified: 2014-07-15
I get this message when I click on a button to run a macro:
"Cannot run the macro ...The macro may not be available in the workbook or all marcros may be disabled."

I do have my macro settings in the Trust Center as "Enable all macros..." and the "Trust access to the VBA project..." enabled
but when I click on the button that is what I get.
And I do have the file saved as a .xlsm and .xls and neither way works. Any ideas?
0
Comment
Question by:mabehr
  • 4
  • 3
  • 2
9 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40195855
Do you have the macro defined as

Private Sub MyMacro

or

Public Sub MyMacro

If so change it to
Sub MyMacro

and move it to a code module (like module1) if it's not already there. You may have to insert a module if you don't have module1.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40195891
It is very likely that the macro has been renamed.

Right-click on the button object (if a Form control object) then select "Assign Macro..." and ensure that it is referring to the correct macro.   The alternate solution is to rename your macro back to the name the button was referring to.

Regards,
-Glenn
1
 

Author Comment

by:mabehr
ID: 40196135
Thanks Glenn. It is pointing to the right macro (there's only one) but maybe my button is not really a button. I just drew a rectangular object and assigned the macro to it. Should that work?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:mabehr
ID: 40196136
@MartinLiss, I'll post a screen shot of what everything looks like tomorrow when I get to work. Thanks.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40196182
Yes your shape should work but I bet your macro didn't show up in the list.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40196236
Martin is correct; a drawing object should work.  However, check carefully the name of the macro seen using the "Assign Macro..." steps I listed above with the name (and possibly path) of the macro in your workbook.
0
 

Author Comment

by:mabehr
ID: 40197058
Attached are screenshots showing my setttings and code but still getting the error.
Screen-Shot-07-15-14-at-08.19-AM.PNG
Screen-Shot-07-15-14-at-08.20-AM.PNG
Screen-Shot-07-15-14-at-08.20-AM-001.PNG
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40197272
Your drawing object (box) is pointing to a macro in another workbook:
List_Distribution_DuplicatesRemoved.xls - workbook
SplitSheettoSheets - macro

You need to re-assign it to "SplitData" by clicking on "SplitData" and then clicking the OK button.
re-assign macro-Glenn
0
 

Author Closing Comment

by:mabehr
ID: 40197298
Ahhh!!! Screenshots always help in discovering the problem. In this case user error. Thanks Glenn Ray. That worked and it now does what it's supposed to do. Thank you VERY much!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

577 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