98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Preventing users from deleting sheets
Preventing users from deleting sheets
Resolved · High Priority · Version 2010
Tue 20 Aug 2013, 16:21
Edited on Tue 20 Aug 2013, 16:49Delegate Matthew said...
Matthew has attended:
Excel VBA Intro Intermediate course
Preventing users from deleting sheets
Is there a way to disable the delete sheet function on Excel 2010 via VBA. The code that I have tried to use from Excel 2003 does not seem to work in 2010. Any suggestions on how to get this to work if it is possible? I can not just simply protect the workbook as this disables other functions with in the workbook
Private Sub Worksheet_Activate()
Set mymenubar = CommandBars.ActiveMenuBar
mymenubar.Controls("Edit").Controls("Delete sheet").Visible = False
End Sub
Private Sub Worksheet_Deactivate()
Set mymenubar = CommandBars.ActiveMenuBar
mymenubar.Controls("Edit").Controls("Delete sheet").Visible = True
End Sub
Wed 21 Aug 2013, 19:58Trainer Gary said...
RE: Preventing users from deleting sheets
Hi Matthew
Thanks for getting in touch. They made this unnecessarily difficult to find in Excel 2010!
The workaround is to enable protection from a single sheet.
The following example will allow you to modify every sheet in the workbook except the protected one. The user will be able to edit the protected worksheet, but they will not be able to rename or move the sheet.
To add this code to a particular sheet, double-click the sheet name in the VBE and add the following two macros:
Private Sub Worksheet_Activate()
ThisWorkbook.Protect Password:="password", Structure:=True
End Sub
Private Sub Worksheet_Deactivate()
ThisWorkbook.Unprotect Password:="password"
End Sub
You can modify the password in both arguments to whatever you need. You should also protect the VBA project (from the VBE window, Tools > VBAProject Properties > Protection).
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 21 Aug 2013, 19:58Trainer Gary said...
RE: Preventing users from deleting sheets
Hi Matthew
Thanks for getting in touch. They made this unnecessarily difficult to find in Excel 2010!
The workaround is to enable protection from a single sheet.
The following example will allow you to modify every sheet in the workbook except the protected one. The user will be able to edit the protected worksheet, but they will not be able to rename or move the sheet.
To add this code to a particular sheet, double-click the sheet name in the VBE and add the following two macros:
Private Sub Worksheet_Activate()
ThisWorkbook.Protect Password:="password", Structure:=True
End Sub
Private Sub Worksheet_Deactivate()
ThisWorkbook.Unprotect Password:="password"
End Sub
You can modify the password in both arguments to whatever you need. You should also protect the VBA project (from the VBE window, Tools > VBAProject Properties > Protection).
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Manually rotating a 3D chartTo manually rotate a 3D chart; |
