JordanCA57
JordanCA57

Reputation: 125

InStr in If Statement

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

Upvotes: 2

Views: 1084

Answers (3)

brettdj
brettdj

Reputation: 55682

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

Upvotes: 3

YowE3K
YowE3K

Reputation: 23974

Without using the Activate (always a good thing to avoid), your code should look like:

Sub CheckSheets()
    Dim sh As Excel.Worksheet
    'Use the Worksheets object instead of the Sheets object
    ' so that we don't try to process any Charts
    For Each sh In ActiveWorkbook.Worksheets
        If InStr(sh.Name, "Benefits") <> 0 Then
            'Pass the sh worksheet, and avoid the old Call syntax
            AddCheckBoxesRange sh
        End If
    Next sh
End Sub

'Change the subroutine to accept a Worksheet as a parameter
Sub AddCheckBoxesRange(wks As Worksheet)
    'by Dave Peterson
    'add Form checkboxes
    Dim c As Range
    Dim myCBX As CheckBox
    'Don't need to declare wks anymore as it is declared as a parameter
    'Dim wks As Worksheet
    Dim rngCB As Range
    Dim strCap As String

    'Don't need to set wks anymore as it is passed as a parameter
    '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

Upvotes: 1

RyanL
RyanL

Reputation: 1276

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

Upvotes: 6

Related Questions