Prevent user from Deleting and Re-Arranging Worksheets using Excel VBA 2013

The code I supplied was not intended to go into a ThisWorkbook sheet, just a standard Module sheet as you've discovered.

When I referred to ThisWorkbook, I was referring to the code that read,

With ThisWorkbook

...

End With

, which might have to be changed to,

With ActiveWorkbook

...

End With

, or...

Set wb = Workbooks.open(<some workbook>)

With wb

...

End with

These assume that the workbook has already been protected (structure only) with the password my_p_word.

Option Explicit

Public Const csPASSWORD As String = "my_p_word"

Sub Add_a_Worksheet()

With ThisWorkbook

.Unprotect Password:=csPASSWORD

.Sheets.Add After:=.Sheets(.Sheets.Count)

.Protect Structure:=True, Windows:=False, Password:=csPASSWORD

End With

End Sub

Sub Leave_Single_Worksheet()

With ThisWorkbook

.Unprotect Password:=csPASSWORD

Do While .Sheets.Count > 1

Application.DisplayAlerts = False

.Sheets(2).Delete

Application.DisplayAlerts = True

Loop

.Protect Structure:=True, Windows:=False, Password:=csPASSWORD

End With

End Sub

I've put the password into a single global variable so you can use it in multiple places but only have to edit it in one place if you change it further down the road. The code uses ThisWorkbook (e.g. the one holding the code module). If you want to work on another workbook you will have to change that to the workbook handle that was opened or perhaps ActiveWorkbook.