Protect a single worksheet?

[General Fear]

I am using Excel 97.

The reason I am asking this question is because I have a Worksheet
that has code in the Worksheet_Change(). Each time the user changes
data in this worksheet the code does stuff to validate the data. A lot
of stuff. So I wrote code to do the checking. The problem is if the
user deletes the Worksheet my code is also deleted. If they rename the
sheet, my code stops working.

I tried to protect a an Excel Spreadsheet. I used Tools menu >
Protection > Protect Workbook. Then I picked protect structure.

Protecting the structure was not good because it protects everything.
I just want to protect the sheet with my code. The user can do
whatever they want to all the other worksheets.

Is there a way to do this.

 

[Nigel]

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.

 

[Gord Dibben]

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", Structure:=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

 

[General Fear]

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", Structure:=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

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?

 

[Nigel]

Thanks Gord for pointing out the need to protect the workbook structure as
well.

As far as the OP referencing the worksheet, it is true that to fully
reference the sheet prevents unexpected changes on other sheets and
depending on how the name is protected, using Sheets("MySheet") does rely on
the sheet being called "MySheet"; once that is changed the code will fail.

Using a sheet codename; which can only be changed in the VBE project window
or by using the default values of Sheet1, Sheet2 etc, will overcome this
limitation.

You may have noticed in the VBE project window that sheets are named e.g.
Sheet1(Sheet1) etc. You can change the codename by clicking on the sheet
in the project list and press F4. The properties are shown, (Name) is the
codename; Name is the tab name. So to select a sheet named MySheet or the
sheet codenamed Sheet1 use

Sheets("MySheet").Select

Sheet1.Select


--

Regards,
Nigel
(e-mail address removed)

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", Structure:=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

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?

 

[Gord Dibben]

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

 

[Gord Dibben]

I do understand what codenames are and how they are used.

Using the codename is OK if all you want to do is point to the same sheet if
it re-named but not much good if the sheet is deleted.

OP wants to prevent a sheet delete hence my posted code which prevents
deletion or rename of "MySheet"


Gord