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?

 

[Fluff]

Is it possible to customize the starting XML to command the proper registry key be set to enable macros automatically upon Workbook open?

NO, that would simply allow all & any malicious code to reap havoc on your computer/network.

I figured perhaps there would be some way to at least detect and trigger an event when macros are enabled.

What would be the point? If macros were disabled it would still by-pass whatever you are trying to do.

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.

That is exactly what it's for & for good reason.

 

[infrequentcoder]

NO, that would simply allow all & any malicious code to reap havoc on your computer/network.

What would be the point? If macros were disabled it would still by-pass whatever you are trying to do.

That is exactly what it's for & for good reason.

So, imagine, for example, a complex user login system - nothing malicious. However, rendered entirely ineffective by Excel's native security features as described in my post. Now you see the dilemma for users.

Actually, that did not necessarily answer how it is not possible to set Macros via the XML code. I do know XML can be used for commands in some ways.

If there is an event to detect when macros are enabled, code can at least be programmed to run on the enable action, which presents several more functional options and would be far superior to hiding a zillion sheets.

 

[Fluff]

If there is an event to detect when macros are enabled,

Not that I'm aware of, but it may be possible with some sort of API call.

Actually, that did not necessarily answer how it is not possible to set Macros via the XML code.

Yes it did. If you could change the settings that way, there would be no point in having the security in the first place.

 

[infrequentcoder]

Not that I'm aware of, but it may be possible with some sort of API call.

Yes it did. If you could change the settings that way, there would be no point in having the security in the first place.

My thought process is more along the lines of Excel developers either not thinking of this possibility or being unable to exclude XML command capabilities at runtime. I wouldn't know how they could only allow certain XML language to run but not the entire language. Then again, I am not a Microsoft developer. So, there could be a way for them to do that, and maybe I just don't know.

 

[Fluff]

Excel macros are written in VBA, not XML so not sure what you are trying to say.
The whole point is, if there was a way to bypass the macro security settings, there would be no point in having them in the first place.

 

[infrequentcoder]

Excel macros are written in VBA, not XML so not sure what you are trying to say.
The whole point is, if there was a way to bypass the macro security settings, there would be no point in having them in the first place.

I am referring specifically to the capacity of XML to be used to set the registry key allowing macros only - not XML being used to operate macros.

I admire the optimism; however, using that philosophy would mean software never has holes in it and is always made 100% flawless according to the reasoning of "If there was a way around feature X, feature X would be pointless. Therefore, it must be impossible to get around feature X." That logic simply isn't true about computers or software as a whole. Apple literally pays individuals to try and find cracks/bugs/etc. in their software. If you are strictly a spreadsheet enthusiast, your thought pattern here makes sense. No disrespect is intended in my tone here

 

[Fluff]

I am fully aware that all software has bugs, etc. But what you seem to be asking for, is a way to breach the security and that is strictly against the rules of this site.

 

[infrequentcoder]

I am fully aware that all software has bugs, etc. But what you seem to be asking for, is a way to breach the security and that is strictly against the rules of this site.

Not at all. I feel I have articulated my concern quite concisely. I am attempting to ascertain how to create a proper user authentication/log-in form that is actually effective and not able to be disabled by the end-user at the flip of a switch. Otherwise, Excel simply cannot be used for that purpose. Nothing malicious. My workaround concepts may be provocative here but innocent nonetheless.

 

[Fluff]

I am attempting to ascertain how to create a proper user authentication/log-in form that is actually effective and not able to be disabled by the end-user at the flip of a switch.

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