Test if Worksheet is not in an Array

[scott]

I've got my sub ClearDataWk() below to loop through all colored cells on the
active worksheet and delete data from colored cells. I also have a MsgBox
warning to give the user a chance to make sure they wish to delete data.

How can I insert some code to either exit or warn if the ActiveSheet is not
contained within a specific list of worksheets?

Something like:

If ActiveSheet not in In Worksheets(Array("dataSheet1",
"dataSheet2"))

So if the ActiveSheet was "defaultVaues" the sub would display a warning
Msgbox warning the user.

CODE: **********************

Sub ClearDataWk()
Dim cell As Range, sh As Worksheet
Dim sMsg, iStyle, sTitle, Response

sMsg = "Preparing to clear data from worksheet: " & ActiveSheet.Name &
vbCrLf & _
"(note: You will not be able to undo this procedure)" & vbCrLf &
vbCrLf & _
"Do you want to continue ?"

iStyle = vbYesNo + vbQuestion + vbDefaultButton2
sTitle = "Clear Data"

Response = MsgBox(sMsg, iStyle, sTitle)
If Response = vbYes Then
'Continue
ElseIf Response = vbNo Then
Exit Sub
End If

For Each cell In ActiveSheet.UsedRange

If cell.Interior.ColorIndex = 19 Then
cell.ClearContents
End If
Next

End Sub

 

[Bob Phillips]

If Not IsError(Application.Match(ActiveSheet.name,Array("dataSheet1",
"dataSheet2"),0))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[scott]

there seems to be a syntax error. can you check it?

 

[Bob Phillips]

Missed the Then

If Not IsError(Application.Match(ActiveSheet.Name, _
Array("dataSheet1", "dataSheet2"), 0)) Then



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[scott]

i should have seen that. thanks.

Missed the Then

If Not IsError(Application.Match(ActiveSheet.Name, _
Array("dataSheet1", "dataSheet2"), 0)) Then



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)