How to hide macro code from a user using VBA

0
votes

I already know how to hide macro code from the user; Simply open up your VBA project and go Tools - VBAProject Properties - Protection and check Lock project for viewing enter a password and your code is made inaccessible to users.

I want to know if this can be automated with VBA.

Why?

I have a sheet of data that I use a macro to scramble, and a key to unscramble. A macro runs on a Workbook_open event and I want to guarantee this runs before the user can access any content.

When a user opens the workbook, they can just hold Shift or choose not to enable macros, I don't want that. So I hide the macro code and protect all the cells by default using a really long and strong password. Part of the Workbook_open code then removes these restrictions (presumably by using the password), and of course the Workbook_open code only runs if macros are enabled.

So can I toggle the viewing status with VBA? I imagine some string of SendKeys would do the job, but not ideal.

1
vbaexcel
There is literally nothing you can do to protect an Excel file in this manner from a deliberate attempt to hack it by a reasonably-knowledgeable user. The best you can do is safeguard against accidental stuff and n00b users. Heck, the user can disable macros via Security setting. There is no VBA you can use that will circumvent or override that. – David Zemens
You do know that is is fairly common knowledge to break a workbook's vba project passwrd..? – user4039065
I would lock the VBproject and Workbook structure, and make the protected sheet xlVeryHidden, and have your Open event unprotect and make it xlVisible. That's the best you can do. In that case if the macros are disabled, the user won't be able to do anything until/unless she hacks your VBProject, and if she's that competent, there's nothing you can do anyways. – David Zemens
There's not much you can do to protect an Excel file in this manner from an accidental non-attempt not to hack it by a completely unknowledgeable user. What do you do when macro security is set too high by policy (or the user) to allow it to run? – Comintern
@Jeeped and in response to David Zemen's first comment; Perhaps I made my intent unclear. I'm aware that Excel security is weak. I wanted to employ this technique as a trigger to mid-level users that macros must be enabled. Without too much detail it's for a challenge, no hacking allowed, just inventive uses of Excel to force the situation on someone, and their task is to look at macros and formulae and unravel the sheet's workings. I don't want to override security settings - I thought I made that clear in the question, the user decides to turn on macros, otherwise the workbook is unusable. – Greedo

1 Answers

0
votes

What about having two spreadsheet? One of them will be for the user, you save it as Excel Workbook only (not Excel Macro-Enable Workbook). So the user won't even be able to see that there is a macro associated to it. The other spreadsheet will be only for you, which you will import all the data the user adds to the other spreadsheet automatically and you can access the macro when you need it.