Solved: stopping users deleting sheets

[philfer]

In Excel VBA is there a way to stop users from deleting or hiding or indeed adding sheets to a workbook i.e. is there a Worksheet_BeforeDelete event?

If not how can I do this in VBA. Would I have to put code behind each sheet to stop it being deleted?

[unmarkedhelicopter]

Protect workbook
but it's not infalible

[philfer]

I'm not sure that would work as users need to use the workbook i.e enter data, do calcs, copy and paste so I cant protect the whole workbook

[mikerickson]

If you put this in the ThisWorkbook code module.

Dim newSh As Boolean

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 Application.DisplayAlerts = False
   newSh = True
   Sh.Delete
 Application.DisplayAlerts = True
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Me.Unprotect
  Call unHideAll
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  If newSh Then newSh = False: Exit Sub
  Me.Protect
End Sub

and this in a normal module

Sub unHideAll()
Dim xsheet As Worksheet
For Each xsheet In ThisWorkbook.Sheets
    xsheet.Visible = xlSheetVisible
Next xsheet
End Sub

It will prevent sheets from being added, deleted or hidden.
It works because a sheet must be deactivated before it is deleted.

[unmarkedhelicopter]

I'm not sure that would work as users need to use the workbook i.e enter data, do calcs, copy and paste so I cant protect the whole workbook

Protecting the workbook is not the same as protecting sheets.
When you protect the workbook you protect the workbook structure, how amy sheets, what they are called, if they are hidden. You can still enter data. Why not read up on a suggestion before you shoot it down ?

[Bob Phillips]

For goodness sake, he didn't shoot it down, he just made a comment on the response. A little explanation of why it might work might have been more constructive, but anyway philfer can ignore it because mikerickson seems to have given him a perfectly workable response.

[unmarkedhelicopter]

For goodness sake, he didn't shoot it down, he just made a comment on the response. A little explanation of why it might work might have been more constructive, but anyway philfer can ignore it because mikerickson seems to have given him a perfectly workable response.

Astute obeservation ...
Seems to work if only they don't disable macros
Some people want it both ways !

[Bob Phillips]

One more improvement in 2007, there is a facility to define trusted locations, so you can store all macro constrained workbooks there, and then turn your security setting to high.

[unmarkedhelicopter]

And where did he
Say he was using 2007 ?
Simply must have missed that !

[Bob Phillips]

He didn't, just an observation on my part.

I was trying to introduce some relevance, some usefulness to the post. On reflection, I agree that my post was only relevant in response to yours, which as he hadn't mentioned that he disabled macros; and as he was posting on VBAExpress then he probably doesn't disable macros, there was probably initial relevance.

I apologise, I won't bother in future.

[mikerickson]

If users want to disable macros and deprive themselves of the benefits of my coding, it fine with me. Their computer, their decision.

[unmarkedhelicopter]

If users want to disable macros and deprive themselves of the benefits of my coding, it fine with me. Their computer, their decision.

Well it seems they want to deprive themselves of philfer's sheets and you would not believe some of the stuff users mess with 'cos they can' ... actually you probably would !!!

[mikerickson]

If users want to mess with stuff 'cos they can', let 'em. It their data on their computer running the code that they decided to let manipulate their data.
Protecting a user against their bad habits (eg.deleting a sheet without checking that the proper sheet is active.)is a feature of good coding (hence, warning screens), but there is no need to take it to extreems. If they want to deliberately input garbage, I don't see any problem when garbage comes out.

[unmarkedhelicopter]

I hear and applaud your point, but generally 'WE' get it in the neck because "Why did you let me do that ?"
Look at the modern legal establishment where (and this is true) a user washed their poodle and dried it in the microwave, they sued the company and won (standard business school case point), now every plastic bag comes with instructions not to pull it over your head ! That's just reducing the number of Darwin awards, muddying up the gene pool and not giving the rest of us something to laugh at.
I once had a multi-person, cross-departmental timesheet system, which was fully protected (as far as Excel allows ) pulled apart because the user wanted to populate a new year but didn't want to read the plentifull instructions about doing just that (cos the 1st if the month usually falls on a different day than the previous year (every time !!!). I also blame the tech guy who broke the password (the user had no chance of doing that !) for allowing a departmental reporting system to be tinkered with by an idiot studying to become a moron.
Rather than enter the year in a designated cell and run the update script, they manually went in and changed every day for every user to a hardcoded value. I rate most users very highly, but some users seem bent on causing themselves pain.
So I applaud your point of view but until PC's come with a loaded firearm pointed at the user and when a prompt comes up and says "Are you now going to do something Stupid ?" and they answer truthfully so the inquest verdict will be "Suicide by use of PC", then I won't hold my breath.