You do have to make sure you are working on the proper sheet so pointing to
that sheet would be a necessity if you are running the macro while another
sheet has the focus.

Your code does that but a slight revision could be made.

Instead of selecting "MySheet" you can use

With Sheets("MySheet")

Do your stuff

End With

To prevent the deletion or rename of that sheet try the code I posted.


Gord

On Sat, 20 Dec 2008 17:20:03 -0800 (PST), General Fear
wrote:

On Dec 20, 1:23*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Nigel

Protecting the sheet will not prevent deletion of that sheet.

This event code placed in Thisworkbook module will prevent the sheet being
deleted or re-named.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name = "MySheet" Or _
* * * *ActiveWindow.SelectedSheets.Count 1 Then
* * * * * * *ThisWorkbook.Protect Password:="justme", Structu=True
Else
* * * * * * *ThisWorkbook.Unprotect Password:="justme"
End If
End Sub

Note: also prevents any grouped sheets from being deleted/copied/moved but
OP can probably live with that.

I will question OP on why the sheetname is hard-coded in the
Worksheet_Change event code?

To prevent users from seeing the password, lock the VBAProject to prevent
viewing the code.

Gord Dibben *MS Excel MVP

On Sat, 20 Dec 2008 16:42:35 -0000, "Nigel"
wrote:

I presume when you say protect you mean the name and deletion of the sheet?

You can protect the sheet having first made the cells you want the use to
edit unlocked.

Instead of using the sheet name (as per tab) use the sheet codename, that
way your users can change tab names without affecting your code.

Thanks for the reply everyone.

So I saw a question aimed at me. Why hardcode the sheet name?

I wrote some code that deletes rows from the worksheet. I noticed that
if I clicked around, the wrong rows was deleted. But when I did
something like

Sheets("MySheet").select
Delete rows here . . .

By selecting the sheet, I was sure that the rows deleted was in the
right worksheet. Not just what happened to be in focus at the time.

Is this not a good idea?