LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 149
Default 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



 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Test Initialization of an Array Neal Zimm Excel Programming 7 December 27th 06 04:58 PM
Test for dups in Array Perico[_2_] Excel Programming 5 July 14th 05 09:51 AM
Array Test VBA Dabbler[_2_] Excel Programming 9 March 30th 05 09:53 PM
Test for end of array of objects? peter Excel Programming 8 February 3rd 05 09:15 AM
Test for Single Character That is in an Array scallyte Excel Worksheet Functions 2 November 11th 04 04:47 PM


All times are GMT +1. The time now is 06:34 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"