How to get around Excel macro warning.
November 4, 2008 6:18 AM Subscribe
How to bypass Excel macro warning or alternative to macro.
posted by emjay to Computers & Internet (13 answers total)
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.