I am just starting to learn about protecting workbook structure.

I'm building an application where the user can add and delete his/her own
worksheets.

There are other sheets in the app, however, that are protected and which I
do NOT ever want to be deleted, they have specific and un-changing worksheet
names.

I was surprised when I was able to delete a protected worksheet, so ......

What are some approaches to do what I want ?

Thanks,
Neal


--
Neal Z

U¿ytkownik "Neal Zimm" napisa³ w wiadomo¶ci
...
I am just starting to learn about protecting workbook structure.

I'm building an application where the user can add and delete his/her own
worksheets.

There are other sheets in the app, however, that are protected and which
I
do NOT ever want to be deleted, they have specific and un-changing
worksheet
names.

I was surprised when I was able to delete a protected worksheet, so ......

What are some approaches to do what I want ?

Thanks,
Neal


--
Neal Z


easist would be to hide your sheets ( if its not necessary to use them)
mcg

When you protect a sheet, you tell the user that they can't change unlocked
cells on that sheet.

But if you protect the workbook (with Windows checked), then the user can't
delete, insert, rename or move a worksheet.

And workbook protection like this doesn't affect what the user can do to any
cell on the worksheet.

I think I'd build a small userform with a listbox that only added the names of
the worksheets that can be deleted.

I put a listbox and two commandbuttons (ok/cancel) on a userform and had this
code behind it:

Option Explicit
Private Sub CommandButton1_Click()

Dim iCtr As Long
Dim myPWD As String

myPWD = "hi"

ActiveWorkbook.Unprotect Password:=myPWD

For iCtr = 1 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCtr) Then
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets(Me.ListBox1.List(iCtr)). Delete
On Error GoTo 0
Application.DisplayAlerts = True
End If
Next iCtr

ActiveWorkbook.Protect Password:=myPWD, Windows:=True

Unload Me

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim wks As Worksheet
Me.ListBox1.MultiSelect = fmMultiSelectMulti
For Each wks In ActiveWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = "sheet1", "sheet3", "sheet5"
'skip it
Case Else
Me.ListBox1.AddItem wks.Name
End Select
Next wks
End Sub


Neal Zimm wrote:

I am just starting to learn about protecting workbook structure.

I'm building an application where the user can add and delete his/her own
worksheets.

There are other sheets in the app, however, that are protected and which I
do NOT ever want to be deleted, they have specific and un-changing worksheet
names.

I was surprised when I was able to delete a protected worksheet, so ......

What are some approaches to do what I want ?

Thanks,
Neal

--
Neal Z

--

Dave Peterson