If语句中的InStr

Question

我需要遍历我工作簿中的所有工作表。如果工作表的名称包含文本“效益”，那么我需要在该工作表上运行一个宏作为活动工作表，然后循环到下一个工作表。

我有这段代码，(1)它以任意方式运行宏，(2)它不循环到下一个工作表。

我是VBA的新手，还没有弄明白这一点。

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

如果不使用Activate (总是要避免的一件好事)，代码应该如下所示：

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

Answer 2

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 3

对你已经收到的好答案做了一些小小的调整：

1. 不需要激活第二个子部分中的工作表，最好直接使用工作表。
2. 将工作表本身传递给主子项，而不是工作表名称。
3. 您正在检查好处，而不是好处，所以使用Lcase$确保您捕获了字符串。
4. 在操作环境时，使用Application.ScreenUpdating = False关闭Excel (虽然不选择工作表将消除大多数屏幕闪烁在本例中)。

呼叫子

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

主子

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