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

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?
mabehrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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.
1
Glenn RayExcel VBA DeveloperCommented:
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
mabehrAuthor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

mabehrAuthor Commented:
@MartinLiss, I'll post a screen shot of what everything looks like tomorrow when I get to work. Thanks.
0
Martin LissOlder than dirtCommented:
Yes your shape should work but I bet your macro didn't show up in the list.
0
Glenn RayExcel VBA DeveloperCommented:
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
mabehrAuthor Commented:
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
Glenn RayExcel VBA DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mabehrAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.