Enable Macros Prompt but No Macros Exist!

Hi,

Now i only post on here once i've scoured the net for existing solutions, but this time have failed.

I'm getting the Enable Maros prompt when opening a spreadsheet that's exported from our ORacle database, but the spreadsheet itself contains no macros, no code, no modules and no hidden/locked sheets.

I can't include the actual spreadsheet as it has sensitive patient & staff data, but have got rid of everything and re-saved and attached that. Still has the enable macros prompt.

Thanks in advance.

Ali

NHS Haringey.

Ali,

Excel will report macros even if you have some modules with no code in it.

Debra has a web page on it at http://www.contextures.com/xlfaqMac.html#NoMacros

Thanks Bob, but already found that post and tried all her resolutions to no avail.  That's why i attached the actual file hoping that an awesome MVP would come to my rescue.

It's not a 'be all and end all' and can easily work around it but it's more annoying then prohibiting.

Thanks anyway Bob, much appreciated.

It seems to be the Option Explicit lines in ThisWorkbook and the Sheet module. Remove them, save it, and it seems fine.

I couldn't find the Option Explicit lines. I looked everywhere I could and didn't find anything in the normal spots. For example, I've seen range names that referred to other spreadsheets do this, but deleting all the names didn't work.

Something is strange in 2007 when I go to Office Button/Prepare/Inspect Document. It tells me there is a hidden sheet, but can't remove it. I don't know how to find that sheet.

Further testing. The range names all referred to a sheet named "macro1". I ran this line of vba code to unhide that sheet.

ThisWorkbook.Sheets("Macro1").Visible = xlSheetVisible

Deleting the sheet was what it took. I take it the sheet had been set with the "very hidden" property at some point.

Omar/Bob,

The sheet had several names defined, i deleted them.

Couldn't see any coding present such as Options Explicit, but i'm not at all VBA savvy so may not have been looking in the correct place.

I'm using Excel 2003. Still not any closer to a solution i feel.

Ali,

I think the Option Explicit was a red herring, my VBIDE inserted them itself, you don't have them.

YOu have to unhide the Macro1 sheet as Omar showed you, and then delete it. Deleteing the names is not sufficient.

Thanks Bob. I tried to do what Omar said but think i may have been doing it wrong.

Could you provide an idiot's steb-by-step guide please?

thanks,

Okay.

With your workbook open, go into the VBIDE - Alt-F11

Make sure that the immediate windo is open - Ctrl-G

In the immediate window, type

Workbooks("Test File.xls").Sheets("Macro1").Visible = xlSheetVisible

and then hit return

Go back to Excel, Alt-F11


The Macro1 sheet should now be visible, so you can delete it as normal

You're a star, you deserve a badge of some kind.

thank you to you and Omar.