PDA

View Full Version : Solved: Protect sheet except for new inserted sheet



gnod
06-18-2007, 07:56 PM
Hi,

how to protect the sheet except for the new inserted sheet? i use this code to protect all my sheets.


' Protect all worksheets
Sub ProtectAllSheets()
Dim sht As Worksheet

With Application
.ScreenUpdating = False
For Each sht In ThisWorkbook.Worksheets
sht.Protect strPassword
Next sht
.ScreenUpdating = True
End With
End Sub


thanks..

lucas
06-18-2007, 08:42 PM
Is it always the last sheet?

gnod
06-18-2007, 11:37 PM
no, it is not always the last sheet..

mdmackillop
06-19-2007, 12:13 AM
Sub AddProtectAllSheets()
Dim sht As Worksheet
Sheets.Add
With Application
.ScreenUpdating = False
For Each sht In ThisWorkbook.Worksheets
If sht.Name <> ActiveSheet.Name Then
sht.Protect "Pass"
End If
Next sht
.ScreenUpdating = True
End With
End Sub

gnod
06-19-2007, 01:48 AM
Sub AddProtectAllSheets()
Dim sht As Worksheet
Sheets.Add
With Application
.ScreenUpdating = False
For Each sht In ThisWorkbook.Worksheets
If sht.Name <> ActiveSheet.Name Then
sht.Protect "Pass"
End If
Next sht
.ScreenUpdating = True
End With
End Sub


but it will only applicable to the newly added activesheet, i want only to protect the worksheet before i give it to the user and everything the user add a sheet to the workbook it will not affect by the ProtectAllSheets code..

for ex:
if i have 10 sheets before give it to the user, only the 10 sheets is protected and everything they add is not protected..

thanks..

Charlize
06-19-2007, 03:17 AM
Sub ProtectAllSheets()
Dim sht, sarray
'names of worksheets are one, two and three
sarray = Array("one", "two", "three")
For Each sht In sarray
Worksheets(sht).Protect "strPassword"
Next sht
End SubBut you can still delete these worksheets with rightclick ... Insert on error resume next to bypass possible error when they delete one of the worksheets that you want to protect for alteration.

gnod
06-19-2007, 03:41 AM
Thanks.. :thumb
i already disable the delete sheet using this code..


' Protect the sheet from being deleted, renamed, moved and copy
Sub Disable_SheetDeleteRenameMoveCopy()
With Application
With .CommandBars("Worksheet Menu Bar")
With .Controls("Edit")
.Controls("Delete Sheet").Enabled = False
.Controls("Move or Copy Sheet...").Enabled = False
End With
.Controls("Format").Controls("Sheet").Controls("Rename").Enabled = False
End With
With .CommandBars("Ply")
.Controls("Delete").Enabled = False
.Controls("Rename").Enabled = False
.Controls("Move or Copy...").Enabled = False
End With
End With
End Sub

Charlize
06-19-2007, 04:20 AM
So they can't delete the sheets that they created or rename them. Maybe use a criteria to see which sheet is active and based on that remove or restore the possibility to delete a sheet ...

gnod
06-19-2007, 06:26 AM
So they can't delete the sheets that they created or rename them. Maybe use a criteria to see which sheet is active and based on that remove or restore the possibility to delete a sheet ...

oh my! i need to revise my code (Disable_SheetDeleteRenameMoveCopy).. :banghead:

how can i modify my code that the user can delete or rename the sheet they created?

thanks..

Charlize
06-19-2007, 02:19 PM
Or maybe use a floating menubar with those commands. If the name of the activesheet is different then the ones in your array, show the menubar else hide it.