InStr in If Statement

Question

I need to loop through all the worksheets in my workbook. If the name of the worksheet contains the text "Benefits" then I need to run a macro on that worksheet as the active worksheet and then loop to the next.

I have this code and (1) it runs the macro either way and (2) it doesn't loop to the next sheet.

I'm new to VBA and haven't been able to figure this out.

Sub CheckSheets()
    Dim sh As Excel.Worksheet
    For Each sh In ActiveWorkbook.Sheets
        If InStr(sh.Name, "Benefits") <> 0 Then
            Call AddCheckBoxesRange
        End If
    Next sh
End Sub


Sub AddCheckBoxesRange()
'by Dave Peterson
'add Form checkboxes
Dim c As Range
Dim myCBX As CheckBox
Dim wks As Worksheet
Dim rngCB As Range
Dim strCap As String

Set wks = ActiveSheet
Set rngCB = wks.Range("B3:E3")
'Set rngCB = Selection
strCap = "Select Plan"

For Each c In rngCB
  With c
    Set myCBX = wks.CheckBoxes.Add _
      (Top:=.Top, Width:=.Width, _
       Height:=.Height, Left:=.Left)
  End With
  With myCBX
    .Name = "cbx_" & c.Address(0, 0)
    .LinkedCell = c.Offset(1, 0) _
        .Address(external:=True)
    .Caption = strCap
'    .OnAction = ThisWorkbook.Name _
'        & "!mycbxMacro"
  End With
Next c

End Sub

Answer 1

Sub CheckSheets()
    Dim sh As Excel.Worksheet
    For Each sh In ActiveWorkbook.Sheets
        If sh.Name Like "*benefits*" Then
            Call AddCheckBoxesRange(sh.Name)
        End If
    Next sh
End Sub


Sub AddCheckBoxesRange(sName)
    ActiveWorkbook.Sheets(sName).Activate
    'by Dave Peterson
    'add Form checkboxes
     Dim c As Range
     Dim myCBX As CheckBox
     Dim wks As Worksheet
     Dim rngCB As Range
     Dim strCap As String

     Set wks = ActiveSheet
     Set rngCB = wks.Range("B3:E3")
    'Set rngCB = Selection
     strCap = "Select Plan"

     For Each c In rngCB
         With c
             Set myCBX = wks.CheckBoxes.Add _
                 (Top:=.Top, Width:=.Width, _
                 Height:=.Height, Left:=.Left)
         End With
         With myCBX
             .Name = "cbx_" & c.Address(0, 0)
             .LinkedCell = c.Offset(1, 0) _
             .Address(external:=True)
             .Caption = strCap
             '.OnAction = ThisWorkbook.Name _
             ' & "!mycbxMacro"
         End With
     Next c
End Sub

Answer 2

Some minor adjustments to the good answer you have already received:

1. No need to activate worksheets in the second sub, better to work with the sheet directly
2. Pass the sheet itself to the main sub rather than the sheet name
3. You were checking for Benefits not benefits so good to use Lcase$ to make sure you are capturing your string
4. Use Application.ScreenUpdating = False to turn off Excel when it is manipulating the environment (although not selecting the sheets will get rid of most of the screen flashing in this case).

calling sub

Sub CheckSheets()
    Dim sh As Worksheet
    Application.ScreenUpdating = False
    For Each sh In ActiveWorkbook.Sheets
        If LCase$(sh.Name) Like "*benefits*" Then Call AddCheckBoxesRange(sh)
    Next sh
    Application.ScreenUpdating = True
End Sub

main sub

Sub AddCheckBoxesRange(ws As Worksheet)

    'by Dave Peterson
    'add Form checkboxes
     Dim c As Range
     Dim myCBX As CheckBox
     Dim rngCB As Range
     Dim strCap As String


     Set rngCB = ws.Range("B3:E3")
    'Set rngCB = Selection
     strCap = "Select Plan"

     For Each c In rngCB
         With c
             Set myCBX = ws.CheckBoxes.Add _
                 (Top:=.Top, Width:=.Width, _
                 Height:=.Height, Left:=.Left)
         End With
         With myCBX
             .Name = "cbx_" & c.Address(0, 0)
             .LinkedCell = c.Offset(1, 0) _
             .Address(external:=True)
             .Caption = strCap
         End With
     Next c
End Sub