Protect workbook for UserInterfaceOnly

Hi all,

From what I've seen online so far, there is no workaround for my problem, so this is my final desperate attempt to find an answer.

Over the past three months, I have been building a database in Excel. It is very complex and extensive at this point. I've put in various layers of simple protection, like password-protecting the individual worksheets, protecting the VBA project code, hiding all inactive sheets automatically, and building a login userform with different levels of access. Everything works very nicely, and I'm pleased with how it has turned out.

It occurred to me yesterday that I should probably find a way to prevent users from adding new worksheets to the database. This is mainly because it would seriously jack up the login code if they did add one. There aren't any people in my workplace who would deliberately try to mess things up, but there are plenty of people here who would mess things up accidentally, and I figured preventing worksheet additions would be another ounce of idiot-proofing.

Here's the problem: if I set up the basic Workbook.Protect method in the Workbook Open event, a whole ton of other code gets messed up. For example, every time the VBA tries to unhide or hide a sheet, the code crashes. Yes, I could go through and lift the workbook protection whenever a sheet needs to be hidden or un-hidden, but we are talking thousands of lines of code, and it would take me a million years, and I'd probably miss a few points where I should have added it and then the code will crash on a user, which is a very bad thing.

Is there any way to set up Workbook Protect so it protects from user interface only? I know I can protect the worksheets this way. This would be a lovely workaround for me. However, from what I've seen online, this isn't possible. PLEASE tell me there is a workaround! I really don't want to have to sit down and go through all the work of protecting and unprotecting on each macro, and I also really don't want users to be able to accidentally add worksheets. For heaven's sake, I accidentally added one the other day! I deleted it immediately, but you can't assume that a user would be sensible enough to do that.

If there isn't a workaround, I'll go through and add the protection and unprotection everywhere. But it would be nice if one of you could save me from that.

I was already aware of this page; it was what gave me the idea of using Workbook.Protect in the first place. And the Workbook.Protect does do exactly what it says: it prevents users from adding, deleting or moving sheets. The problem is that a lot of my current code will hide or un-hide various worksheets as part of the process, and apparently that counts as moving a worksheet because if I set Workbook.Protect, the macros crash. That was the whole problem: I'm looking for another way to do this that won't mess up my current code.

I'm probably going to end up doing exactly that. The problem is that means I now have to go through about eighty thousand lines of code and remove all the protect and unprotect stuff. Sigh.

Oh, no, it's all structured and very neat. There's just a whole lot of it. We're talking over twenty userforms alone, let alone modules and all the code attached to each worksheet.

I'm going through it one window at a time. It's probably not a bad thing that I'm re-reading all of the code. I can check the formatting and make sure I have titles on all of my message boxes, and stuff like that.

I think I'm just going to go through it manually. I'm already halfway through it now, and I've only been at it for half an hour, so it won't be as bad as I thought. Thanks!

Originally Posted by TMS

Ah, fair enough. I can't help then, sorry. I must admit to rarely, if ever, using UserInterfaceOnly preferring to use UnProtect ... do something ... Protect again.

But, in reality, you could use UserInterfaceOnly to cater for the changes you make on the worksheet(s) and just UnProtect and Protect the workbook when you want to hide stuff.

Regards, TMS

This subject still not solve... We create forms and protect workbook so that the macros can do the hide and hiding while the users cannot accidentally move the sheet or hide them..

With your suggestion to manually protect the wb it takes away the concept of the user and admin.