[xfm]

I created a macro in an Excel worksheet and later decided I didn't want it. After deleting it, it no longer shows up in the macro listing.

However, whenever I open the worksheet, I get a macro warning with the options to enable or disable, etc. AND my laptop is unable to go on standby mode when this particular file is open, presumably because of the presence of an active macro.

How can I get rid of the macro COMPLETELY? Can I somehow transplant the data to a "clean" new Excel worksheet?

Appreciate your help.

[Advertisements]

Do you have many formulas in the spreadsheet?

If so.

1. Replace all = with # in every sheet.
2. Set up your new spreadhseet with all the Tabs named exactly as they were.
3. Copy all Data into the New Spreadsheet.
4. Close old copy.
5. Replace all # with = in every sheet.

You should now have a clean copy.

[jasonharrod]

Do you have many formulas in the spreadsheet?

If so.

1. Replace all = with # in every sheet.
2. Set up your new spreadhseet with all the Tabs named exactly as they were.
3. Copy all Data into the New Spreadsheet.
4. Close old copy.
5. Replace all # with = in every sheet.

You should now have a clean copy.

[xfm]

Appreciate your response, but this worksheet is an annual budget with separate sheets for each month, with a total rollup of the year. as you can imagine, it is filled with formulas. To modify each formula manually and then change back is probably equivalent to generating a totally new worksheet.

Unless there is a more efficient way, I'll either have to live with the inconveneiences or generate a whole new spreadsheet from scratch.

But thanks anyway. I appreciate you taking the time and effort to help.

[Octagonal]

Hi xfm,

What you need to do is, if you have no other macros in the workbook delete Module1 using the Visual Basic Editor. To do this follow these steps.

• Open the particular workbook that you are having this problem with.
• From the Tools menu select Macro|Visual Basic Editor

The Visual Basic Editor will open and you must follow these instruction exactly. DO NOT explore or change anything else in here, unless you know what you are doing.

• In the pane on the left hand side of the screen you will see a folder titled Modules, click on the + sign next to it to expand the folder.
• This will open the folder to view Module1 beneath it.
• Right click on Module1 and from the drop down menu select Remove Module1.
• When asked if you want to export the module, click No.
• From the File menu select Close and Return to Microsoft Excel.
• Close the workbook and save the changes.

Re-open the workbook and your problem should be solved.

If you have other macros in the workbook (ie. in other worksheets) don't change anything and let me know.

Octagonal

Edited by Octagonal, 17 March 2006 - 12:54 AM.

[xfm]

Hi Octagonal. Thanks for the help.

Tried your method. When I open Visual Basic Editor, I get two rows of menu toolbars at the top of the screen, and a vast solid gray area underneath.

There is no pane on the left. I looked in the 'View' submenu, and didn't see anything obvious that might open the pane you referenced.

Any further advice? Based on your cautions, I didn't want to poke around too much.

[Octagonal]

Give me a few minutes and i'll dig a little deeper.

Octagonal

[Octagonal]

Ok, i'm working with XL 2002 here so a quick question.

Did you create the Workbook yourself?

In Visual Basic Editor from the View menu select Project Explorer and let me know if the left hand pane(Project Explorer) appears.

Octagonal

Edited by Octagonal, 17 March 2006 - 08:12 AM.

[xfm]

That was the key!

Expanded the Modules tree and deleted Module 1. I'm now going to save the workbook under a new name and reopen to see if the macro warning still comes up.

Thanks again for your help.

[xfm]

Looks good.

When I open the "new" workbook, no macro warning appears.

When I open the original, I still get the macro warning.

So now I'll just save the new file under the original filename, and I'm golden.

Appreciate you sharing your expertise.

[Octagonal]

xfm

Should work without any hitches. Glad to be of help.

Octagonal

[hay123]

Why removing? "cause so far I have not seen any problem regarding with that program..

Hay
______________________

Advanced Excel Training

[ExcelDummy]

Hi there!
I am very new here - and not an excel geek at all - I had the same issue as the person who had a warning concerning macros 'enable' 'disable' - I followed the instructions in the response to him but can not see Module1 in the Visual Basic Editor option of Excel - Under Project - VBAProject the tabs with their respective names are listed and none has an option to be removed - probably a good thing because I would have tried to remove them -
I am not sure other than clicking the option 'Macros' where else to look into to find macros in this workbook or excel spread-sheet.
As far as I can tell there are none - yet I get the warning.

Any ideas any one - thanks!

ExcelDummy

[krishnap2w2]

I have encountered a similar problem in excel. I resolved it. I will explain how to handle the enable macros in excel

 

In Excel, macros are disabled by default for enhanced security. Below are the steps to

Enable Macros in two different versions of Excel:

Excel 2007 Version

Excel 2010 Version

 

Attached Files

•  How to enable macros in excel.pdf   234.4KB   815 downloads