CODEWITHSUNDEEP
vbaexcel
JordanCA57
JordanCA57

Reputation: 125

CountA Function in Loop

I have the following code that adds checkboxes to worksheets with the text "benefits" in the sheet name.

I need a checkbox in row 3 for each column that has text in row 5. Each worksheet will have a different number of columns.

The code below works, but sets and then uses the columnCount for the first worksheet for all the worksheets.

I think I have the right code, but have it placed in the wrong order??

    'Checks for the text "Benefits" in the sheetname.
    'If true then runs AddCheckBoxesRange Macro to add selection checkboxes for each plan.
    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


'Macro CheckSheets looks for the text benefits in sheetname, if it exists it calls this macro
    Sub AddCheckBoxesRange(ws As Worksheet)
    'add Form checkboxes
    Dim c As Range
    Dim myCBX As CheckBox
    Dim rngCB As Range
    Dim strCap As String

    Dim columnCount As Integer
    columnCount = WorksheetFunction.CountA(Range("5:5")) + 1
    Set rngCB = ws.Range("B3", ws.Cells(3, columnCount))

    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(37, 0) _
            .Address(external:=True)
        .Caption = strCap
      End With
    Next c

    End Sub

Upvotes: 0

Views: 683

Answers (1)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

You just need to qualify the columnCount otherwise it will always look at the ActiveSheet.

Change

columnCount = WorksheetFunction.CountA(Range("5:5")) + 1

to

columnCount = WorksheetFunction.CountA(ws.Range("5:5")) + 1

Upvotes: 1

Related Questions