Macro won't go away in Excel 6

[JAE]

I wrote a simple spreadsheet to tabularize various staff assignments and anticipated hours for each week. In developing the spreadsheet, I added a couple of simple macros to help in formatting some cells. After further development, I decided that I didn't need the macros, so I deleted them. They were just simple Excel command sequences, tied to a button on the spreadsheet.

Now, whenever I open the spreadsheet, a window pops up asking if I want to enable or disable the macros. There are no macros in the spreadsheet but this window keeps popping up every time. Is there any way to get rid of it?

 

[IJR]

JAE

I guess you are facing a problem which I always face. The damned Excel has so many places where it stores the macros. And someone told me that most errors we do are in where we store the macros especially with VB code modules.

I am not able to solve your problem but I suggest you check out that line of thinking.

Good luck man

IJR

 

[tigrek]

ENABLE/DISABLE MACROS

There is a checkbox on properties tab of Excel that switches this on/off.

Also, when the mesage pops up, is there not a check box saying next time do not ask this?

I remember there was one.

 

[JAE]

No, I looked for a checkbox like that and there was none...only "enable" "disable" and "cancel". I'll check out the properties tab and get back to you.

Thanks...

 

[dsi]

Copy the entire contents (worksheets) of the workbook into a new workbook. I think that something is left in the file database so it thinks there are still macros associated with the file. This is the only way I know to get rid of this error.

Hope this helps! DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[swertel]

Running MS Office 2000.

In Excel 2000, you pick Macro (Alt+F8) from the Tools, Macro menu. The list of macros appears in either "All open workbooks, this workbook, or other." Select the macro you wish to delete and click delete. Close the dialogue and save the file. This is probably how you deleted the macro.

To "cleanly" remove the macro:
Select Visual Basic Editor (Alt+F11) from the Tools, Macro menu. You will see several condensed menu items and probably only (1) item that is expanded. It should be labeled "VBAProject (yourfilename.xls)." Within that item will be two folders: Microsoft Excel Objects--containing the number of sheets in the workbook and "ThisWorkbook," and Modules.
Delete the modules folder. Exit VBA and save your Excel workbook. You will no longer get the intro pop-up message to enable or disable macros.

Cheers, --Scott Wertel
scottw@interfaceforce.com

http://www.interfaceforce.com

 

[JAE]

swertel
Thanks a bunch....it worked just as you said.

dsi: Thanks also, I thought of that but wanted to see if there was a cleaner way.

 

[IJR]

Thanks Swertel

The storing of macros in Excel used to make me crazy man

Regards
IJR

 

[swertel]

FYI:

I wrote a FAQ about this subject which just restates what I said in the earlier message. I put it there so when this thread gets burried 20 levels deep, the FAQ is there as a reminder. I'll probably be the one using it the most. ;-) --Scott Wertel
scottw@interfaceforce.com

http://www.interfaceforce.com

 

[IJR]

JAE, Swertel, Tigrek,dsi and all of you folks

Nothing but thanks for this cool discussion. Always saves me chunks of time.

By the way guys, while following this thread, I played a bit with my macros (I mean VB editor). And from that moment on I have this autosave dialog popping up evey time I type in a cell or modify. The only relaxing thing is that it prompts me to save and it is a bit small in size(laugh!!). Dont laugh at me guys, you might face it one day.(Another laugh).

IJR

 

[swertel]

I accept your challenge.

To me is sounds like your autosave add-in is haywire.

Under the Tools menu, pick Add-ins... to see if your Autosave add-in is checked. If not, I'll have to work this out more. If so, read below.

From the Tools menu select Autosave.... If you have autosave turned on (in addition to the add-in being checked/loaded), there should be a checkmark next to the Autosave... menu item.
The Autosave dialogue appears and the first line should be checked to "automatic save every" X number of minutes. IJR, your time is probably set very low. Unchecking this box turns autosave off, but the add-in is still loaded. (Notice no checkmark by the Autosave... menu item when you uncheck this first box and click OK.)
You get the prompt because the last item is checked, "Prompt before saving." You can verify that only the active workbook gets autosaved also, otherwise that may be a reason for continuous autosaves.

Let me know if I'm on the right track. --Scott Wertel
scottw@interfaceforce.com

http://www.interfaceforce.com

 

[IJR]

Thanks swertel

You are right on Add-ins being with Autosave loaded. Well I could turn that off. But if you want to go on further: here is a bit of a nuisance- The Tools menu in my version of office 97 contains no Autosave option. And there is nowhere to set parameters on.

I probably did not install the Office properly. For now I will turn the add-ins - Autosave check box and relax for a while.

Another thing I might try to do is to get to some Excell initialization file (kind of .ini or whatever) and mess it a bit to look up for autosave duration number

Thanks Swertel and keep around, we need you guys

 

[swertel]

I don't remember Office 97 very well, show here's a shot in the dark.

Look under the Options in the Tools menu under the general tab or save tab.?.?.?.? I vaguely remember there being an autosave line (turn on/off checkbox with # minutes) somewhere in the Option dialogue box. Sorry I can't help you more. --Scott Wertel
scottw@interfaceforce.com

http://www.interfaceforce.com

 

[IJR]

Yeah Swertel

I will make sure you help in a different thread though.
Thanks and nice weekend
IJR

 

[Mala]

IJR

For the AutoSave menu item to appear in the 'Tools' menu, you have to install it first. If your Office installation is correct, AutoSave must be present in the add-ins library. What you do is:

Tools>Add-ins and check the box next to Auto-Save.
Doing this will load the add-in in memory and by clicking Tools>AutoSave you can specify the time after which you are prompted to save.

Of course you can uninstall it whenever you wish by Tools>Add-ins and deselct the box next to Auto-Save. This won't remove the file from disk and u can re-install later if you wish.

Forgive me if I've already replied to this somewhere else...

Mala
mala_rs_singh@rediffmail.com

 

great! i've been looking for the clean-Macro-removal for a long time! cheers!

1 more thing about it... in the VBA editor, under "Microsoft Excel Objects", each worksheet is listed. u may have to remove any remnant code from each worksheet.

to do this:
1. double click each sheet listed to open its code window.
select General from the drop-down list, then click in the main code window.
(note: dont worry about "Worksheet" in the drop-down menu...leave that alone)

2. press ctrl-A to select all, then press Delete.
3. close that code window and move on to the next worksheet.
4. when u've removed all remnant code from all sheets, close VBA editor, and return to Excel. Save your workbook.

i don't know why, but i had to use this method, as well as deleting the Modules folder like Swertel said. hope that helps.
thx again.

 

[SnowCrash]

You guys are certainly the experts, but I was wondering if this could be resolved with a 'save as...' command?

Crashj