Macro warning when no macros in workbook

  • Thread starter Thread starter BillS
  • Start date Start date
B

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
 
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 **
 
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.
 
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
 
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
 
Nope, nothing.

Confused yet?(like me?)
Ken Wright said:
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
-------------------------------------------------------------------------- --
Click to expand...
 
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).
 
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?
 
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....)
 
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.
 
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
 
Back
Top