How to replace Excel's delete warning (for one visible worksheet) with custom message?

[Steve Rutkey]

Hello,

I have a workbook that has 4 hidden worksheets, plus 1 or more worksheets unhidden and available to the user. I'm using worksheet protection but not workbook protection. Users are free to copy and delete worksheets, but there should always be one "user" worksheet present, in addition to the 4 hidden worksheets. Fortunately, a built-in Excel requirement almost addresses this: when user tries to delete the last visible worksheet, the Excel warning comes up, "A workbook must contain at least one visible worksheet. To hide, delete, or move the selected sheets(s), you must first insert a new sheet or unhide a sheet that is already hidden".

Unfortunately, that warning tips the user off that there are hidden worksheets. I have not instituted "very hidden" or other tricks to keep them from finding the hidden worksheets (which are protected at least) - my users are not advanced in Excel and I don't think will go exploring. But this message will encourage them to. Also, if they do unhide at least one other, they'll be able to go ahead and delete the last user worksheet, and they shouldn't be able to.

How can I replace the standard message with a message of my own? (e.g., "You must leave at least one user worksheet available in this workbook. Please click Cancel.") I think that requires error handling targeting that message, but as a novice with VBA I don't understand whether I can place such handling in the workbook module, or how to implement.

Thank you for any direction on this.

Steve

[AlphaFrog]

I have not instituted "very hidden" or other tricks to keep them from finding the hidden worksheets (which are protected at least) - my users are not advanced in Excel and I don't think will go exploring.

To me, hiding the sheets xlVeryHidden would be the most simple solution. It's an easy thing to do. They are not going to find them unless they try hard.

[Steve Rutkey]

AlphaFrog,

Thank you for your guidance. I hadn't used very hidden before (only read about it while looking for other things)...but you are right, it is very easy to use. I had macros that were setting visibility to False and changing those to xlSheetVeryHidden does the job I needed. Thank you for the quick response! I will mark as solved.

Steve