Macro warning when no macros in workbook

[BillS]

I have a user who has several Excel workbooks that give the macro warning
when opened, even though there are no macros in the workbooks. His security
is set to high, and I can arrange to lower the setting if it is required,
but would prefer to leave it set at the companies default setting (high).
Does anyone have any idea why a workbook with no macros would trigger the
warning>

Thanxz

 

[Paul B]

Bill, You also have to remove the modules on which the macros were stored
have a look here for details
http://www.contextures.com/xlfaqMac.html#NoMacros
--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Ken Wright]

You need to delete any empty modules that housed those macros.

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you need to
search for
your workbook's name, and when you find it you may need to click on the + to expand it.
Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1
Module2
etc etc (You may have just 1 of these)

If you have named your sheets then those names will appear in the brackets above as
opposed to
what you see at the moment in my note.

Right click on the modules and select remove. When prompted with a question re exporting,
just
hit no. Then hit File / Close and return to Microsoft Excel and save the file.

 

[BillS]

Ken:

I did as you suggested, but "Modules" does not appear under the workbook
name, Just "Excel Objects". Any other suggestions? Would this change if he
e-mailed me the files, rather than me looking at them on his system?

BillS

 

[Ken Wright]

Doubleclick on the ThisWorkbook object and see if there is any code in there - If so then
delete the code and save the workbook. Him emailing you the file shouldn't make any
difference - The macro or module would have to be in the file that was being opened at the
time to generate the warning message

 

[BillS]

Nope, nothing.

Confused yet?(like me?)

Doubleclick on the ThisWorkbook object and see if there is any code in there - If so then
delete the code and save the workbook. Him emailing you the file shouldn't make any
difference - The macro or module would have to be in the file that was being opened at the
time to generate the warning message

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

-------------------------------------------------------------------------- --
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------------------------- --

 

[Ken Wright]

When you open the file, do you get prompted with the warning message??

 

[Ken Wright]

Also try doubleclicking the sheet modules themselves to see if you have any event macros
in there - Again just delete them if you do (Once you know what they were doing of
course).

 

[BillS]

Yep, on file open, on a system with security set to high.

 

[Ken Wright]

OK, if you've hit the point where you are fed up, then by all means mail it on down if you
can, and I'll happily take a look. You'd need to take the NOSPAM bit out of email address
though. - Just checking though, you did see my last note where I said to try
doubleclicking the sheet modules to look for any event macros?

 

[BillS]

Yep, tried that too, I think I'll just let him keep answering the security
warning, he tends to be annoying anyhow, so...


(Yep, I'm a professional. no really....)

 

[Ken Wright]

OK - Your call, but I'm quite happy to take a look at it for you if you want, so the
offer's there. You could even delete the data out and send it. In fact, that might be a
way to narrow it down. With a copy of the book - Insert a new sheet and then delete all
the others. save the book and reopen - If no macro warning, then recopy the original and
delete all sheets but the first - save and reopen, and just try and home in on what sheet
if any is causing the problem. Maybe you have some controls with macros in somewhere -
who knows.

 

[Michael D. Ober]

Add-Ins will cause this warning as well. What Add-Ins does he have?

Mike.

 

[barry]

I don't know why it happens, other than at one time the
workbook may have contained a macro. I had the same
problem with one of my clients and solved it by saving it
to another Excel version level and then back to the
current version. The warning went away.
Barry

 

[David McRitchie]

Chip Pearson has an explanation and a macro to remove macros.
http://www.cpearson.com/excel/vbe.htm
Though I think Debra's suggestions would be the same manually.
You might try the macro see if it does better for you, or reports an
error.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Jerry W. Lewis]

When you check the workbook, worksheet and form code pages (as described
by Ken Wright, etc.) use Edit|Select All and press the delete key. Even
spaces on these pages will trigger the warning.

If the warnings still persist, look at

http://groups.google.com/groups?threadm=etcItvDs$GA.194@cppssbbsa04

Solver leaves hidden names that some versions of Excel mistakenly
interpret as Excel4 macros.

Jerry