VBA - restrict access to specific sheet on shared document

[MisterProzilla]

Hi all,

I have a shared workbook, in which a certain sheet contains confidential information. I want to restrict access, if not to the entire doc, then at least to this sheet. Just after some advice if anyone has done this before

• The first problem is that I can't encrypt the whole doc with a password as it's a shared file, so that option's out.
• On opening the confidential worksheet, I've using VBA to hide all data and request a password - if entered incorrectly, you're kicked back to the front sheet. However, it only works if macros are enabled, so anyone could open the workbook and bypass security by just ... not enabling it.
• I can set up a secure splash page; this is where all sheets are hidden with VBA on closing except a front splash page - on opening you see the splash page with a button to unlock & unhide sheets, so you're forced to enable macros in order to use the button and gain access. Only issue here is that, on a shared workbook, I don't know how one person closing & hiding all sheets will affect another user with it open at the same time. Also, macros could just be disabled after the button is used, so there's that too - I guess I could just set the password at this point so it's effectively encrypting it in a roundabout way.

Any advice on this? It's stored on a fairly secure shared drive so it's not as if it's just lying around on desktop, but with new data protection laws coming in soon it's important that I make it as secure as possible first time

Thanks for looking

 

[Kyle123]

You can't do this securely, it's not possible. Excel VBA security is trivially easy to bypass - if it really is confidential, then although it may not be what you want to hear, you're using the wrong tool.

If you require a password to open (even though you appear to have ruled it out), it is many orders of magnitude more secure than any of your proposed solutions.

 

[MisterProzilla]

I'm happy to rule it in if possible But it seems that shared workbooks don't support encryption (one of their many limitations). You're right, this document would be much better suited to a database, but as I'm unfamiliar with Access I'd like to avoid having to rebuild it if possible. I've managed to bootstrap a (very inefficient) relational database in Excel, only realising afterward that Access would have been better