Ignoring End Statement

[davegb]

I got help here to use the Workbook sheet change event to protect some
worksheets. But the code first tests to see which sheet in the workbook
the user is changing, and allows it on some, prevents it on others. But
the End statement I put into the case doesn't execute, it's just being
ignored. Any ideas on what's going on here?

Private Sub Workbook_SheetChange(ByVal ws As Object, ByVal Target As
Range)

For Each ws In ActiveWorkbook.Worksheets
If Right(ws.Name, 7) <> "Monthly" Then 'ignore these sheeets
Select Case ws.Name
Case "TOTALS", "CONTACT SUMMARY", "CONTACT SUMMARY by type",
"(Code Key)" 'Excluded sheets
End <----IGNORED
Case Else 'if password hasn't been entered yet, then ask
for password
If bPwrdEntrd = False Then
' Call EnterPassword(ws)
Call PasswordEntry
End If
End Select
End If
Next
End Sub

Thanks again!

 

[davegb]

Try Exit Sub instead

thanks for your help.
I changed it to Exit Sub. But XL doesn't seem to recognize the excluded
sheet names, so it never exits. I tried switching to code names, but
that doesn't work either. Any suggestions?

 

[Dave Marden]

Here is the code I use for protecting the sheets, thought you may be
interested. This macro allows you to either protect all sheets with the
same password or individualize the sheets.

This Macro can be simply inserted into any excel workbook, and it will work
like a champ as it it.

Please let me know what you think,

Dave Marden

There are separate Sub's for Protect and for Unprotect.


Sub Protect_All_Sheets()
'
' Protect_All_Sheets Macro
' Macro recorded 3/3/2001 by David Allen Marden
'
' This Macto Can Be Used In Any Excel Project
'
' Keyboard Shortcut: Ctrl+Shift+P
'
'
Dim CurrentSheetName As String
Dim Password As String
Dim CheckPassword As String
Dim Decision As String
CurrentSheetName = ActiveSheet.Name
Sheets(1).Select
'Check if sheet is protected.
If ActiveSheet.ProtectContents = False Then
Do While ActiveSheet.ProtectContents = False
'If not, get a password
Password = InputBox("Enter a password for this sheet")
CheckPassword = InputBox("Re Enter Password for this sheet")
If CheckPassword = Password Then
'Set Password
ActiveSheet.Protect (Password)
Else: MsgBox ("Passwords did not Match")
End If
Loop
End If
Decision = InputBox("Typing G will copy last password to all other
sheets, Typing I will individualize all sheets, If you type anything else
then the first page protected is the only page that will get protected.")
If Decision = "i" Or Decision = "I" Then
For i = 1 To Sheets.Count - 1
ActiveSheet.Next.Select
'Check if sheet is protected.
If ActiveSheet.ProtectContents = False Then
Do While ActiveSheet.ProtectContents = False
'If not, get a password
Password = InputBox("Enter a password for this sheet")
CheckPassword = InputBox("Re Enter Password for this sheet")
If CheckPassword = Password Then
'Set Password
ActiveSheet.Protect (Password)
Else: MsgBox ("Passwords did not Match")
End If
Loop
End If
Next
End If
If Decision = "g" Or Decision = "G" Then
For i = 1 To Sheets.Count - 1
ActiveSheet.Next.Select
If ActiveSheet.ProtectContents = False Then
ActiveSheet.Protect (Password)
End If
Next
End If
Sheets(CurrentSheetName).Select
Range("A1").Select
End Sub
Sub Unprotect_All_Sheets()
'
' Unprotect_All_Sheets Macro
' Macro recorded 3/3/2001 by David Allen Marden
'
' This Macto Can Be Used In Any Excel Project
'
' Keyboard Shortcut: Ctrl+Shift+U
'
'
Dim CurrentSheetName As String
Dim Password As String
Dim CheckPassword As String
Dim Decision As String
CurrentSheetName = ActiveSheet.Name
Sheets(1).Select
'Check if sheet is protected.
If ActiveSheet.ProtectContents = True Then
Do While ActiveSheet.ProtectContents = True
'If not, get a password
Password = InputBox("Enter the password for this sheet")
'Reset Password
ActiveSheet.Unprotect (Password)
Loop
End If
Decision = InputBox("Typing G will use the last password to unprotect
all other sheets, Typing I will individualize all sheets, If you type
anything else then the first page protected is the only page that will get
unprotected.")
If Decision = "i" Or Decision = "I" Then
For i = 1 To Sheets.Count - 1
ActiveSheet.Next.Select
'Check if sheet is protected.
If ActiveSheet.ProtectContents = True Then
Do While ActiveSheet.ProtectContents = True
'If not, get a password
Password = InputBox("Enter a password for this sheet")
'Reset Password
ActiveSheet.Unprotect (Password)
Loop
End If
Next
End If
If Decision = "g" Or Decision = "G" Then
For i = 1 To Sheets.Count - 1
ActiveSheet.Next.Select
If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect (Password)
End If
Next
End If
Sheets(CurrentSheetName).Select
Range("A1").Select
End Sub