Prevent Delete of Certain Sheets

[CaptRon]

I've searched for an answer and can't find it, so I'm throwing this out there hoping someone can help me.

I have a workbook that contains 4 sheets (Start, Master, End, Service Totals) that I do not ever want deleted. The user must have the ability to create a duplicate one of them (Master). The additional sheets are placed between 'Master' and 'End' so that formulas grab SUM info from each and provide cumulative figures to 'Service Totals'

At the beginning of a new fiscal year, the office staff can delete the middle sheets to setup the workbook for the new year and/or new personnel.

The problem is.....if they do a group delete and accidentally grab one of the 4 sheets I want to always keep, if goes bye-bye, too.

I am looking for code that somehow sets these 4 sheets apart and will never permit them to be deleted, under any circumstances. Duplicated, perhaps, but not deleted.

I have attached the file for your review. The password to open for data entry is "sa" for now. Understand this is a ways from finished, and if you see anything else that I need to fix or improve, I'm always open for recommendations.

Thanks,

[lucas]

Hi Ron,
What if you have a macro set up to delete all sheets except the 4 you wish to keep....let them run that and no problems...

Option Explicit
Sub delSheets()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
   If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" And ws.Name <> "Sheet4" Then ws.Delete
Next
Application.DisplayAlerts = True
End Sub

[Norie]

Ron

Why not just use a template?

Then you wouldn't even need to delete anything in the first place.

[CaptRon]

We have 12 inspectors in our office. Most of these will be assigned to the unit the following year, though throughout the year there are 2 to 4 inspectors that transfer in or out. Once assigned to OAI (Office of Audit and Inspection), their stats stay part of the workbook for the FY, even if they leave mid-year.

So the deletion of sheets only occurs during the setup phase for the new FY. If I have them delete all sheets except the static 4, then they have to create and name the 12 middle sheets for the inspectors in OAI at the beginning of the new FY.

I might want to use an approach I used on an activity summary in one of the Highway Patrol districts where I created a setup table listing the troopers in the area. The column list of 15 trooper names provided the sheet names for the worksheets and determined if they would be hidden or visible.

I'd also have to build in a "clear out old data" macro......wait, better, keep a clean copy of the original workbook, then copy and paste the names from last years workbook to the new years setup table for a fresh start.

Don't know which approach would be best.

Ron

[CaptRon]

Perhaps that would be best. I've done that before. You have to allow some for expansion, in and out of personnel to the unit, so I built it with more sheets that I expect they might need and hide those not in use at the time. If they have a group for entry, monthly summary, annual summary, etc. the thing might be 45-50 sheets large.

I was trying to keep the workbook as compact as possible, but this create/delete approach is more complicated.

Ron

[Norie]

Ron

I actually meant a template with just the 4 sheets.

[CaptRon]

Steve,

What about this? I create a column table that represents the names of the inspectors in the office. The table would allow about, say 15 or maybe 20 names. The create a macro that will build a sheet for each of the inspectors in the list (number of inspectors) and name each sheet according to the names of the inspectors listed. Actually, I would take the sheet names from another table I create to truncate the name to last name only.

I think that might work. Gotta try it anyway, just for fun!

Ron

[CaptRon]

I actually meant a template with just the 4 sheets.


That's probably the best choice overall, even if they create a sheet for each inspector and never delete any for the entire FY, setting up 12 new sheets for each beginning FY with a few clicks of a mouse is SO MUCH easier than what they are doing now.

Thanks to all.

Ron

[lucas]

Here's one more possiblility for you Ron. The listbox will not even show sheets 1, 2 and 4 but they can delete any of the other sheets using the form....see attached.

[CaptRon]

Steve,

Great idea. They can create what they what they need, then delete from the list box, if necessary when the time comes.

The remaining sheets will still be packed with last years old data, though. I'll either have to clear it out, or just begin with a new fresh template as Norie suggested.

Thanks.

Ron

[mdmackillop]

Hi Ron,
You can protect the workbook structure, which prevents insertion/deletion of sheets, so forcing macro use to insert/delete sheets as required.

[CaptRon]

Hi Ron,
You can protect the workbook structure, which prevents insertion/deletion of sheets, so forcing macro use to insert/delete sheets as required.

I have a macro on board that responds to a button click and first removes workbook protection to allow activesheet delete. The issue is that IF the user preselected a group of sheets BEFORE clicking the button, the action is taken on all of the selection. Fine, unless the selection inadvertently included one of the 4 sheets I mean to preserve. I'm trying to make this bulletproof, if possible.

Not meaning to be critical or derogatory of anyone, I've come to believe that you must build your product to exist and function in the hands of the dumbest employee you could possibly hire, not the brightest one in the shop.

Ron

[lucas]

Sub Goodbye()
   Dim ws As Worksheet
    Application.ScreenUpdating = False
    ActiveWorkbook.Unprotect "sa"
    For Each ws In Worksheets
   If ws.Name <> "Master" And ws.Name <> "Service Totals" And ws.Name <> "Sheet4" Then ws.Delete
    Next
End Sub

[CaptRon]

Sub Goodbye()
Dim ws As Worksheet
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect "sa"
For Each ws In Worksheets
   If ws.Name <> "Master" And ws.Name <> "Service Totals" And ws.Name <> "Sheet4" Then ws.Delete
Next
End Sub

This is a quick way to dump everything but the original 4 sheets I need to keep. Thanks.

Ron