Solved: Preventing renaming sheets

[gmulhall]

Hi,

What's the best way to prevent a user

1. Renaming a sheet
2. Deleting a sheet
3. Unhiding a hidden sheet

Thanks,

Geoff

PS is there a way I could have submitted a boolean search through the forum to see if this had been answered by searching for posts containging all the key words like rename, hide, delete and sheet ?

[XLGibbs]

You could have tried an advanced search...

You can go to Tools>Protection>ProtectWorkbook
or protectworksheet. If it is protected, it can't be changed.

Protect Workbook >Structure would prevent deleting a sheet.

If you are really looking to lock it down, excel is not the best way. Anyone with even the slightest motivation can easily get into sensitive data on hidden sheets.

[gmulhall]

Thanks.

Can this be done using VBA code ? ie so that it can be unprotected, updated by my app, and then reprotected ?

Thanks again for your help.

Geoff

[XLGibbs]

Yes the code is fairly simple..

Sheets("Sheet1").Protect "password"

Sheets("Sheet1").Unprotect "password"


Password is option.



How many sheets need protecting/unprotecting during the update? You can pass the sequence through a For each loop of each ws if necessary.

[gmulhall]

Thanks. There are 3 sheets.

One sheet contains the controlsource for a number of the fields on a user form and is thus hidden from the user. It is unhidden during the initialize for the form and then hidden by the vba code when it is presented to the user.

The other 2 sheets are open for user update - but if the user changed the sheet names or deleted one of these sheets my vba code would fail as they are referenced by name "Input" and "Output" in my code.

Thanks again,

Geoff

[XLGibbs]

You can change the References to those sheets to their "codename" instead of the tab name...This would be much more prudent of a programming choice for something that gets distributed in excel.

Sheet1 for example will be Sheet1 regardless of what they rename it.

The sheet that is the controlsource shouldn't need to be visible to be used by the form.

In the VBE you can change the Sheets property to xlVeryHidden. It won't appear in the Unhide menu of the regular workbook, and will remain hidden.

[gmulhall]

Thanks - very useful !

I do need to still ensure the user does not delete the 2 sheets though.

Geoff

[XLGibbs]

Protecting the structure, and not contents of the workbook should be fine.


ThisWorkbook.ProtectStructure = True


ThisWorkbook.ProtectStructure = False

[johnske]

You can change the References to those sheets to their "codename" instead of the tab name...This would be much more prudent of a programming choice for something that gets distributed in excel.

Sheet1 for example will be Sheet1 regardless of what they rename it.

The sheet that is the controlsource shouldn't need to be visible to be used by the form.

In the VBE you can change the Sheets property to xlVeryHidden. It won't appear in the Unhide menu of the regular workbook, and will remain hidden.A point worth mentioning here is that the sheets codename can be changed in the VBE window to something more meaningful than Sheet1, Sheet2 etc. (this can be done manually but not programmatically) and it then doesn't matter whether users later change the sheets name (the 'tab' name).

As an example, you may have named your default Sheet1 as "Annual Profits" and be referring to it in your code with (say) Worksheets("Annual Profits").Activate or Sheets("Annual Profits").Activate. If you change the codename to AnnualProfitsSheet it becomes more meaningful when reading through the code and can then be referred to by just using AnnualProfitsSheet.Activate

[XLGibbs]

Great Point Johnske

[gmulhall]

Much appreciate your all responses. Thank You !

Geoff

[gnod]

this is the code i use for protecting the sheet from being deleted and renamed..


' Protect the sheet from being deleted and renamed
Sub DisableCommand_Enable()
With Application
With .CommandBars("Worksheet Menu Bar")
.Controls("Edit").Controls("Delete Sheet").Enabled = False
.Controls("Format").Controls("Sheet").Controls("Rename").Enabled = False
End With
With .CommandBars("Ply")
.Controls("Delete").Enabled = False
.Controls("Rename").Enabled = False
End With
End With
End Sub

' Unprotect the sheet from being deleted and renamed
Sub DisableCommand_Disable()
With Application
With .CommandBars("Worksheet Menu Bar")
.Controls("Edit").Controls("Delete Sheet").Enabled = True
.Controls("Format").Controls("Sheet").Controls("Rename").Enabled = True
End With
With .CommandBars("Ply")
.Controls("Delete").Enabled = True
.Controls("Rename").Enabled = True
End With
End With
End Sub

[gmulhall]

Thanks gnod - appreciated.

Geoff

[gmulhall]

Thanks everyone for your assistance.

This thread can be marked solved.

Geoff:bouncy:

[Ken Puls]

Just as a further addition to this... I noticed the comment near the top about unprotecting/reprotecting via code.

You can avoid this by setting protection's userinterfaceonly argument to True. The problem is that it isn't held between sessions, so it needs to be applied in the Workbook_Open routine.

What it does, effectively, is make sure that the worksheets are all protected again, but makes the protection applicable to the UI only. Your VBA routines have a free reign to ignore the protection.

Just thought I'd mention it. :)

Oh, and Geoff, you can mark your own thread solved by the Thread Tools menu if you feel it is complete. ;)

[gmulhall]

Thanks for your post Ken. Appreciated.

I'll mark the thread as solved.

Geoff

[Aussiebear]

Ken, just so I can understand this properly. I have a workbook with different vba routines running on different sheets, all of which I have physically set the sheet protection to on.

This then requires the sheet in question to be unprotected, to run a macro or vba routine, then re-protect the sheet.

Your saying that if I were to set the protection UserinterfaceOnly argument to True then the routines will run without having to unprotect / re protect all the time. How do I do this?

Ted

[Ken Puls]

Hi Ted,

That's correct. The key, though, is that the userinterfaceonly setting needs to be reapplied each time the workbook is opened. For some reason MS decided to make this setting evaporate between sessions.

So basically what you do is put the code in the ThisWorkbook object:
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="MyPasswrod", userinterfaceonly:=True
Next ws
End Sub

Cheers!

[Aussiebear]

Thanks Ken