Locking a sheet from being deleteable!

[Hamidam]

Well, I guess the title says it all... Locking a sheet from being
deleteable!???

Thanks / Jennifer

 

[Dave Peterson]

Tools|protect|protect workbook
(Protect the structure)

No sheet will be able to be moved/deleted/inserted/renamed

 

[Hamidam]

Thanks very much!

Jennifer

 

[Hamidam]

Well, now the option of renaming the sheet disappears... they ar
supposed to represent company name... hmm...

Jennife

 

[Dave Peterson]

Yep.

That's why I included this warning:

No sheet will be able to be moved/deleted/inserted/renamed

Maybe you can provide a macro that would unprotect the workbook, rename the
activesheet, and then reprotect the workbook.

Something like:

Option Explicit
Sub testme01()

Dim myResp As String
Dim PWD As String
Dim myErrorNumber As Long

PWD = "passwordhere"

myResp = InputBox(Prompt:="Please enter new name for: " & vbLf & _
ActiveSheet.Name)

If Trim(myResp) = "" Then
Exit Sub
End If

ActiveWorkbook.Unprotect Password:=PWD

On Error Resume Next
ActiveSheet.Name = myResp
myErrorNumber = Err.Number
Err.Clear
On Error GoTo 0

ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=PWD

If myErrorNumber <> 0 Then
MsgBox "Invalid name, please try again!"
End If

End Sub

 

[Hamidam]

Oooh thanks again Dave... her is a snapshot of the final result now tha
Iam finished and since you have been with me from the beginning!

Thanks / Jennife

+-------------------------------------------------------------------
|Filename: Snapshot.GIF
|Download: http://www.excelforum.com/attachment.php?postid=5159
+-------------------------------------------------------------------

 

[Dave Peterson]

I'm one (of many) who don't connect through excelforum. So I can't see the
picture. But I'm glad you got it working.