"Can't find macro" error

[JoAnn]

I have a simple macro that executes the following:

Sub SAVE_Quit()
ActiveWorkbook.Save
Application.Quit
End Sub

I assigned it to a toolbar button called "Save List". Whenever I try to run
the macro from the toolbar, I get the "can't find macro error". But I can
successfully run the macro from the Tools > Macro > Macros list.

The sheet is protected but I still get the error when I unprotect it.

The macro is stored in Module 1. I tried moving it to This Workbook or Sheet
1 (in case it was stored in the wrong place) but got the same error.

What am I doing wrong?

Thanks!

 

[Dave]

Hi JoAnn,
Try reassigning the macro to the button you are using. Do this by un-
assigning first, then reassigning. The current assignation may have an
error, or may have become corrupt.
Regards - Dave.

 

[JoAnn]

Thanks, Dave. That seemed to fix it.

 

[Gord Dibben]

You may have neglected to enter the path to the macro in the assignment.

i.e. MyWorkbook.xls!MyMacro


Gord Dibben MS Excel MVP

 

[JoAnn]

I'll watch for that too ... thanks!
--
JoAnn

You may have neglected to enter the path to the macro in the assignment.

i.e. MyWorkbook.xls!MyMacro


Gord Dibben MS Excel MVP

 

[JoAnn]

Well, looks like I spoke too soon. The first suggestion did fix it so the
macro can run from the toolbar but now I have another problem.

It doesn't save changes made to the active worksheet & I don't have a clue
why.
I'm using Excel 2003 with an XP system. The spreadsheet resides in a
Sharepoint server.

 

[Dave]

Hi JoAnn,
Does the macro save the changes when you run it from Tools > Macro >
Macros list?
Dave.

 

[JoAnn]

This is bizzarre... it saves changes made to the backup whens it run in
Sharepoint but it doesn't usually save them to the active worksheet (which I
have done before it saves to the backup). I did get it to work for a short
while when I ran it from the Macro > Macros but then it stopped working.

I got it to run successfully from the toolbar button just once. But I just
tried it again & it failed. Everytime I test I get something different.
Right now it errors out on code that runs when the workbook is open( to
enable Analysis Tool Pak) but , the workbook is already open when I try to
run the save macro so that shouldn't be happening. I've also rebuilt the
toolbar button several times without any change in behavior.

The more I test, the more fragile it gets. I had the best success when I
tried it the 1st few times. But getting out/re-entering Sharepoint and/or
Excel isn't improving the situation today.

The doc is protected, so I tried rebuilding the toolbar & button unprotected
then protected it. Still no good.

Any thoughts?

By the way, thanks for all your advice -- you folks are awesome!

 

[Alan124]

I have a simple macro that executes the following:

Sub SAVE_Quit()
    ActiveWorkbook.Save
    Application.Quit
End Sub

I assigned it to a toolbar button called "Save List". Whenever I try to run
the macro from the toolbar, I get the "can't find macro error".  But I can
successfully run the macro from the Tools > Macro > Macros list.

The sheet is protected but I still get the error when I unprotect it.

The macro is stored in Module 1. I tried moving it to This Workbook or Sheet
1 (in case it was stored in the wrong place) but got the same error.

What am I doing wrong?

Thanks!

Hi,

I think you should check your excel file first. And if it corrupt, you
can also try a utility called Advanced Excel Repair to repair your
Excel xls file. It works rather well for my corrupt Excel xls files.
Its web address is http://www.datanumen.com/aer/

Hope this helps.

Alan

 

[JoAnn]

Okay, thanks for the tip ... I'll try that.

As a workaround, I found that executing a Save As to the original filename
seems to work within Sharepoint even though the original code works fine from
my PC. So, I'm doing that for now. But I'll look into the corruption issue as
you suggested.

Thanks!

 

[Gord Dibben]

JoAnn

The guy is a spammer and hpoes that people like you will think your workbook is
"corrupt" so you can buy his snakeoil.

Generally when assigning a macro to a button you preface with the workbook name.

MyWorkbook.xls!MyMacroname


Gord Dibben MS Excel MVP

 

[JoAnn]

Thanks, Gord.
--
JoAnn

JoAnn

The guy is a spammer and hpoes that people like you will think your workbook is
"corrupt" so you can buy his snakeoil.

Generally when assigning a macro to a button you preface with the workbook name.

MyWorkbook.xls!MyMacroname


Gord Dibben MS Excel MVP