Prevent Workbook From Opening (VBA)

[markchap]

We have a workbook that we don't want given to unauthorized users. Each time anyone attempts to open the workbook, our code will check an online SQL table to make sure certain environmental variables (and company name) are equal to the approved values. If that test fails, we want the workbook first to display a message to the user and then NOT to open. Naturally, we don't want the user to be able to hit a key combination (such as Ctrl-Break) to interrupt the macro so that the workbook can be used.

Someone else is writing the code to check the online SQL table for approved values. I've tried putting my part of the code in the On Open event, but when my dialog box (using msgbox) displayed to inform the user that the security check failed, I was able to Ctrl-Break out of that code and right into Excel's debug/end dialog box and then into the very spreadsheet whose access was to be prohibited.

If it matters, I tested this before locking the VBA code. I will do so after finishing the code.

If more details are required, please ask. My preliminary code worked as planned (workbook never opened) as long as I didn't try to Ctrl-Break out of it. We want to make sure that this workbook cannot be used even if an unauthorized individual gets a hold of it.

If there are any other "gotchas", I'd appreciate being told about them.

Thanks as always!!!!
Mark

 

[Macropod]

Your scheme is fundamentally flawed in that is requires the user to allow your workbook's macro to run; if they don't it will open without any attempt at verification. And, as you've discovered, users who let the macro run can also Ctrl-Break out of the code before it's finished.

 

[Jerry Sullivan]

Mark,

You can handle the scenario you describe by using this statement...

 Application.EnableCancelKey = xlErrorHandler

...which will send execution to the active error handler instead of to the debugger.

That being said, be aware that Excel worksheet protection and VBA Project protection provides relatively limited security.

 

[Macropod]

Cross-posted at: Prevent Workbook From Opening (VBA) | Windows Secrets Lounge

Please read our policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[MarktheMacroman]

Try putting this at the start of your macro in the "on open" macro

Application.EnableCancelKey = xlDisabled