Reputation: 125
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
Reputation: 55682
Some minor adjustments to the good answer you have already received:
Lcase$
to make sure you are capturing your stringApplication.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
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
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