CODEWITHSUNDEEP
excelvba
BuckT49
BuckT49

Reputation: 3

Perform Action IF the active sheet is one of a group of sheets

I'm trying to clear rows in a worksheet.

I found ActiveSheet.Range(range).Clear.

I have similar looking worksheets in the workbook and I am trying to protect myself from accidentally deleting stuff I need.

I am trying to check if the active sheet is one of 7 worksheets in the workbook. If it isn't, don't clear.

If Not ActiveSheet.Name = Worksheets("014").Name Then

Else
    ActiveSheet.Range("40:42").Clear
End If

This works for a single worksheet, but I need to check for 6 more sheet names. I can code this with multiple "IF NOT" statements checking for each sheet, but is there a shorter, cleaner way to check all seven worksheet names in the same "IF NOT" statement?

Upvotes: 0

Views: 994

Answers (2)

Zev Spitz
Zev Spitz

Reputation: 15337

I would use a Scripting.Dictionary to hold the names of the sheets you are interested in checking against:

Dim dict As New Scripting.Dictionary
dict.Add("Sheet1", 1) 'the actual values don't matter; we want to check against the keys
dict.Add("Sheet2", 1)
dict.Add("Sheet3", 1)
dict.Add("014", 1)

Then we can check if the key exists in the dictionary:

If dict.Exists(ws.Name) Then
    ws.Range("40:42").Clear
End If

You could also use an array:

Dim sheetNames(4) As String
sheetNames(0) = "Sheet1"
sheetNames(1) = "Sheet2"
sheetNames(2) = "Sheet3"
sheetNames(3) = "014"

but since there's no built-in way to check for the existence of an item in the array, you have to write such a function yourself:

Function ContainsItemm(col As Variant, item As Variant) As Boolean
    Dim x As Variant
    For Each x In col
        If x = item Then
            ContainsItem = True
            Exit Function
        End If
    Next
End Function

and use it thus:

If ContainsItem(sheetNames, ws.Name) Then
    ws.Range("40:42").Clear
End If

Upvotes: 2

OpiesDad
OpiesDad

Reputation: 3435

Put all of the sheet names into one string and then use that.

Dim strSheetNames as String
Dim ws As Worksheet
Set ws = ActiveSheet
strSheetNames = ",Sheet1,Sheet2,Sheet3,014..," 'include all sheet names
If InStr(strSheetNames,"," & ws.Name & ",") <> 0 Then
      ws.Range("40:42").Clear
End If

The comma searching trick is per Zev Spitz.

Upvotes: 0

Related Questions