[Edit] - system is acting up this morning, so I apologize in advance if this
turns out to be a double post - JLatham on behalf of an overworked server.
OK, first a little about the 'why' of macro security settings. Basically
macros are Visual Basic for Applications (VBA) code. With code and the right
permissions on a system, just about anything can be done to/with the system.
Since most people run Windows under an Administrator account, the permission
to make changes to anything is there. This means that if there is any
malicious code included in a macro within a program that it can perform its
mischief without any problem. So whether or not you permit macros to be
executed (Enabled) depends pretty much on your trust of the source of the
application and its code.
There are several levels that you can set Macro Security to and I feel that
the one combining the 'best' of security and convenience is MEDIUM. At the
medium setting you are presented an alert when you open the .xls file
containing macros telling you that they exist and asking if they should be
allowed to run (Enabled) or not (Disabled). It is just one extra click to
make that decision. It is the level that I run at and I 'put up with' the
extra click - and most of my .xls files contain code and 99% of that code is
code that I wrote and yet I still run at that level. This keeps me from
being unpleasantly surprised if someone sends me an Excel file that should
just perhaps have a formula or two and some information on a worksheet but
announces that there's code also - then I can look at the code to see what it
is going to do before it gets a chance to do it. Or, if I didn't know how to
read the code, I might just fire a message back to the sender asking "why the
code? what's it doing for me?".
OK - how to set the macro security level. Pretty easy in Pre-2007 versions.
From the main menu toolbar choose Tools | Macro and expand the list to see
the Security option. Click it and then choose MEDIUM in the window presented
to you and click [OK]. Close Excel. The next time you use Excel that will
be the security level used. Excel must be closed and then reopened after a
security level change for that change to take effect.
With Excel 2007 you have some added options. Begin by clicking the Office
Button and then choosing the [Excel Options] link at the lower right of the
window. Choose Trust Center from the left pane of the Options window. Click
the [Trust Center Settings...] button in the right window pane. Click on
Macro Settings in the left pane and to get the equivalent of MEDIUM security,
select the "Disable all macros with notification" option. Click the [OK]
button to close that window, then click the [OK] button to close the Excel
Options window. Close and reopen Excel and you will have 'medium' security
in Excel 2007.
One of the added options in Excel 2007 is to designate 'trusted locations'.
A trusted location can be any location that you can get to from your machine.
Typically it would be a specific folder on your hard drive or another
computer in a network. When you designate a trusted location, then any files
that you put into that location become trusted and if they have macros in
them, even with another level of security chosen, they will open and enable
macros to run without 'warning', saving you a step. You designate trusted
locations through the Excel Options | Trust Center | Trust Center Settings
window also. If you elect to use Trusted Locations, make sure you don't put
files into those locations until you truly do trust them.
Hope this helps with your understanding of Macro Security within Excel (and
other Office applications). As for the workbook I provided a link to, I
think you'll find it does what you wanted very nicely with the help of the
VBA code I wrote, 'trust me' <g>.
