Test if Worksheet is not in an Array

S

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
 
B

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)
 
B

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)
 
S

scott

i should have seen that. thanks.

Bob Phillips said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top