How to get around Excel macro warning.

November 4, 2008 6:18 AM

How to bypass Excel macro warning or alternative to macro.

Working in Excel and need to "highlight" when others replace a formula with a manually entered value.

For different reasons, this complicated, multi-sheet, multi-user spreadsheet cannot be locked or protected as 2 dozen individuals make periodic changes to it and since they often enter information incorrectly, we want some formatting to make the errors stand out when they enter it, or for the oblivious, make it easier for the reviewer to catch the problems.

I do not want alternatives to Excel (Open Office, Google, etc) nor do I want recommendations to use something besides a spreadsheet.

What I need is an easily implemented patch/fix to disable the macro warning (A Digital signature is not an option) or an alternative, non-macro, way to implement the same setup below.

NAME is defined as "FormulaCheck"
which Refers to "=GET.CELL(48,INDIRECT("rc",0))"

The affected cells are formatted with a standard RED Fill Color
They are then Conditionally formatted with Formula is "FormulaCheck" with the conditional format to be a fill color of pale blue with no other conditions (FormulaCheck is a True/False)

This allows for the formula to display the normal visual look and formatting when all is well and if someone enters data manually that overwrites a formula it will stand out in RED. The intent is so that the user, or the reviewer will easily be able to locate the screw-ups.

The problem with this older macro language is that it also requires the user to click on "enable" or "yes" or some variation to allow macros to run whenever they open the document. This is done by the many individuals a few times a day.

My google-fu has failed me and I need some alternative to disable the macros or to try it in VBA (which I'm really not familiar with.)

Bonus points if it is easy to implement so that I can document the use for other spreadsheets the users may want to try this on.

posted by emjay to computers & internet (13 comments total)

Have you tried setting the macro security level to "Low?"
posted by Diskeater at 6:48 AM on November 4, 2008

Are you talking about the macro security warning that pops up every time you open a document with a macro in it?

If so, go to Tools > options > security (tab). Click Macro Security. And select low.
posted by NormandyJack at 6:48 AM on November 4, 2008

Also...since this is a security feature, I don't think you are going to find any nifty VBA code that'll turn it off for you. That'd be pretty stupid, now wouldn't it.
posted by NormandyJack at 6:50 AM on November 4, 2008

What if you do this: make an exact copy of the original worksheet and keep it in the same workbook. Protect that original copy so nobody can change it. Then for each cell in the editable sheet, set the conditional formatting so it checks if that cell is the same as the cell in the protected sheet; if it's not the same, then have the conditional formatting highlight that edited cell.
posted by inigo2 at 6:55 AM on November 4, 2008

@all - Setting Security - LOW
Besides the inherent risks with that, the intent is to correct it in the spreadsheet and not have the users change settings (which is it's own hassle) or opening themselves up to malicious scripts.

@inigo2 - We do have a "template" worksheet that is performing this function. The code/formatting is entirely on this sheet. As stated before, we cannot lock/protect this sheet either as nearly all the users add to this sheet as well so that changes propogate to the other sheets. It's really freakin' complex and I wish they would allow an alternative usage of another application. Current petition is in place to do so but I need an Excel solution in the meantime.


Anyone know how to get the same effect in VBA which can be applied to the Workbook without requiring user intervention or reducing security?
posted by emjay at 7:59 AM on November 4, 2008

Would an Input Mask be too cumbersome in this case?
posted by Nick Verstayne at 9:15 AM on November 4, 2008

@Nick Verstayne - I'm not sure how an input Mask would help here. The formula is just calculating numbers and displays a total:

ie. "=Sum(A5:A10)" would display "$1750".

But the users occasionally just type "$1750" thereby overwriting the formula "=Sum(A5:A10)" Either way, it's numeric numbers.

Did you have a specific application of this in mind?
posted by emjay at 9:30 AM on November 4, 2008

I poked around and couldn't find anything to answer your question specifically, although if you google for "excel show formula as text" maybe you could find something to help.

Another idea - What if you just made sure that if they DO overwrite the formula, the value is what it's supposed to be if they had just used the formula. This will need you to add a column and use conditional formatting.
Add a column (Column D) that has the same formula (Column C) so that they will both display the same result if used correctly . Make the column so skinny as to be invisible. If the user types over the formula in Column C, and the amount does not match what it's supposed to be (The amount in Column D) conditional formatting will kick in and highlight the amount in Column C.

To add the conditional formatting, highlight Column C and go to Conditional Formatting. Condition 1=Cell Value is Not Equal To Column D.
posted by amethysts at 9:52 AM on November 4, 2008

Whoops, this should be:
Condition 1=Cell Value is Not Equal To D1
posted by amethysts at 9:53 AM on November 4, 2008

Make the column so skinny as to be invisible.

Just an aside: you can right-click on the column header and select "Hide" from the context menu to make the column really, truly invisible instead of "really skinny and basically invisible for practical purposes." (Selecting across a range of headers that contains invisible columns and choosing "Unhide" from the context menu reverses this.)
posted by letourneau at 10:26 AM on November 4, 2008

Which version of Excel? In 2007, there is an option in the Trust Center Settings (Office Button --> Excel Options --> Trust Center) which lets you add folders as "Trusted Locations." It works both on the local drive and on networked drives (you have to check "Allow Trusted locations on my network" before you add it, if you want that option).

Workbooks opened from trusted locations will not see the security warning. It's a machine-specific setting. So you'll have to have each user make the change. But you only have to make it once for each directory. It cascades down for child directories, too.
posted by wheat at 12:28 PM on November 4, 2008

@wheat - Not all users are in Excel 2007 so this is a no-go

Because this formatting process is in use throught the workbook, I cannot just hide a column with the comparison or formatting data.

Anyone with VBA skills know how this macro could be done in VBA?
posted by emjay at 8:54 AM on November 6, 2008

Setting security down to Low does, in fact, work. Just not how I wanted it to. Currently dealing with the macro warning until we migrate to the newer version of Office. Thanks to all for your advice.
posted by emjay at 9:26 AM on December 5, 2008