Test if Worksheet is not in an Array
 

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

Test if Worksheet is not in an Array
 

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" wrote in message
...
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

Test if Worksheet is not in an Array
 

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

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

Test if Worksheet is not in an Array
 

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" wrote in message
...
there seems to be a syntax error. can you check it?

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

Test if Worksheet is not in an Array
 

i should have seen that. thanks.

"Bob Phillips" wrote in message
...
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" wrote in message
...
there seems to be a syntax error. can you check it?

"Bob Phillips" wrote in message
...
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" wrote in message
...
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