Microsoft Excel - REQUIRE MACROS

[infrequentcoder]

Hello, Mr. Excel users! Excel w/ VBA are such a great duo when used in tandem. It seems one is able to lock out all use of Excel via VBA events and also use Auto_Open() to bypass the application.enableevents if it set to false. However, the one issue I am running into time and time again is that the Trust Center can ultimately override all events or macros, effectively rendering any VBA code useless until authorized.

Now, I know there is a common solution floating around the internet to require macros, but it is weak at best. You may be familiar. The idea is to set all sheets to "xlveryhidden" and toggle them with the workbook open/close events. For one, that method can be incredibly slow and visually glitchy depending on the number of sheets. It also does not prevent a user from opening an external workbook and extracting data from the "xlveryhidden" sheets via VBA into the external workbook. This "solution" is truly just a visual cue at best and not air-tight.

As a result, I figured perhaps there would be some way to at least detect and trigger an event when macros are enabled. I have recently begun digging more into MS Access and there is a famous stock template for a fictitious company called Northwind Traders. One thing I have noticed, is that there actually is some way to detect when macros are enabled in this template. I am wondering if this feature is exclusive to Access only? If so, do you have any idea why?

Finally, I also know that there is the ability to manipulate some of the backend XML of a workbook. Is it possible to customize the starting XML to command the proper registry key be set to enable macros automatically upon Workbook open? I am uncertain of how to write this in XML if it is at all possible. If this IS possible, how would this translate to Macbooks, etc.? Is there any way to customize the starting XML to prevent loading on any system other than Windows?

 

[infrequentcoder]

Then use the correct tool for the job, namely a database.

I prefer pushing Excel to its limits as it is my program of choice for nearly all things data. When used appropriately, it is far more secure, flexible, and visually customizable than most database options on the market. Not to mention, Excel can be accessed and updated entirely offline with no subscription.

Access has some pros to it but is more rigid in design and concept.

 

[Joe4]

If security is truly a concern, then Excel is probably not the right tool to use.
As has been mentioned, the security on it is less than stellar.
At best, the security mainly prevents users from "accidentally" stumbling into something they shouldn't.

 

[Fluff]

When used appropriately, it is far more secure, flexible, and visually customizable than most database options on the market.

That I would strongly disagree with. Excel is not, never has been & never will be secure.

 

[infrequentcoder]

That I would strongly disagree with. Excel is not, never has been & never will be secure.

When saved in Binary format and containing a masterfully complex 255-character password, nothing short of quantum computing or an FBI backdoor will access the file.

 

[Fluff]

Which has absolutely nothing to do with your original question.

 

[infrequentcoder]

Which has absolutely nothing to do with your original question.

That might be since the conversation has shifted to the topic of Excel security in large. Not to mention it will automatically be more secure than many database options simply for the fact that it can be operated without any internet connectivity. The age of the internet has created a vast disparity between the security of modern databases and former.