If statement works here, case doesn't

[salgud]

The code below uses a Sheet_Change event to allow or not allow other
changes to the sheet. It applies to some sheets in the workbook, but not to
others. The Select Case statement determines which is which and bypasses
the process of looking up a password for the appropriate sheets.

I tried to write a case statement to circumvent the process for all sheets
with the word "Monthly" in their title, but couldn't make it work. I
finally put the entire Case loop inside a If statement and it works fine.

' PUBLIC VARIABLES FOR THIS MODULE ARE DECLARED IN
MODPASSWORDCHANGE
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
'Activated by a change in any worksheet in the workbook - gets password
from user
' verifies password, and unprotects worksheet

Dim vResponse As Variant
Dim sWsName As String

Set wsPwrdNames = ThisWorkbook.Sheets("Passwords")
Set rShNames = wsPwrdNames.Range("ShNames")
Set rPwrdEnt = wsPwrdNames.Range("bPwrd")

If rPwrdEnt.Value = "True" Then Exit Sub 'EXIT
sWsName = ActiveSheet.Name

If InStr(sWsName, "Monthly") = 0 Then < CAN THIS BE DONE WITH ANOTHER CASE?

'If the sheet name is "Totals" or "(Code Key)" skip to end of sub
Select Case sWsName

'If the spreadsheet name contains "monthly", skip the password code
Case Is = "TOTALS", "(Code Key)", "Provider Wtg"
Application.EnableEvents = False

Case Else
Set rFoundShName = rShNames.Find(sWsName, _
LookIn:=xlValues, _
LookAt:=xlWhole)

If rFoundShName Is Nothing Then
MsgBox "There is no password listed for this sheet!",
vbExclamation, "Missing Password"
GoTo Errhndlr 'EXIT
End If

wsPwrdNames.Visible = True

PwrdForm:

ufPwrdEntry.Show

If sPwrd = rFoundShName.Offset(0, 1).Value Then
bPwrdEntrd = True
Application.EnableEvents = False
rPwrdEnt.Value = bPwrdEntrd
Application.EnableEvents = True
Else
vResponse = MsgBox("Incorrect Password! Click OK to try again,
Cancel to exit", _
vbOKCancel)

If vResponse = vbCancel Then

Errhndlr:
ufPwrdEntry.Hide
Application.EnableEvents = False
On Error Resume Next
Application.Undo
On Error GoTo 0
Application.EnableEvents = True
wsPwrdNames.Visible = False
End 'EXIT
Else
GoTo PwrdForm
End If
End If

wsPwrdNames.Visible = False
End 'EXIT
wsPwrdNames.Visible = False
Application.EnableEvents = False
On Error Resume Next
Application.Undo
On Error GoTo 0
Application.EnableEvents = True
Application.ScreenUpdating = True
End Select
End If
End Sub

So is this an instance where a Case statement isn't appropriate, or am I
just mis-applying it?

 

[OssieMac]

Try the following

Select Case InStr(sWsName, "Monthly")
Case Is > 0
'Code here if "Monthly" does exist in name
Case 0
'Code here if "Monthly" NOT in name
End Select
Next ws

You could also use Case Else in lieu of Case 0 or leave it out altogether if
you don't need any code for sheet names that do NOT contain Monthly.