How do I protect my workbook if macros are disabled?

[ajmoore28]

I have a workbook that I have customized to the extreme including hidding worksheets, various userforms, and a lot of other automation. I have it pretty secure accept if the user disables macros. If that happens they can get into everything including my VBA coding. How can I work around this without just putting it out there in good faith?

I appreciate the help!

 

[Tom Urtis]

Not that you'd want to or are saying so, but just to get it out of the way, you cannot control what another user wants to do with their security options, it's their computer.

However, what do you mean with this:

"they can get into everything including my VBA coding."


No one, certainly not me, is touting Microsoft's security platform as comprehensive, and to be fair Microsoft never represents its products as secure. However, you can still password protect and lock the VBE which will at least introduce a burden to experienced would-be password circumventers, and in most cases would stymie the average user. So have you done that, which still asks the user for a password regardless of the user's security settings or disabling of macros.

 

[ajmoore28]

Yes, actually I have password protected the VBE so hopefully that will work. I was just curious if there was a way to disable the entire workbook if macros were enabled to ensure the data integrity remains intact. I have all the worksheets protected and the macros are what protects and unprotects each sheet so I'm hoping that will work as well. Thanks for the feedback...

 

[Tom Urtis]

You lost me, was this you wrote:

"I was just curious if there was a way to disable the entire workbook if macros were enabled to ensure the data integrity remains intact"

really supposed to be this:

I was just curious if there was a way to disable the entire workbook if macros were not enabled to ensure the data integrity remains intact

 

[ajmoore28]

Yes, that is correct...thanks Tom.

 

[Tom Urtis]

If macros are required to be enabled, one little trick you can pull is to append your worksheet formulas with conditional Ifs that nest a UDF that does nothing but return a null string. Because UDFs are VBA, they would be rendered baseless and all the formulas would return a #NAME? error when macros are disabled.

With the worksheet's formula-containing cells looking like an Andy Warhol painting, users would be induced to enable macros, unless they feel like staring at or printing reports with nothing but #NAME? in the cells.

Note that the values in those cells can still be extracted with link formulas from another workbook, while that subject workbook is closed.