mercredi 8 avril 2020

Using OnAction to add a macro to each checkbox, and only the first checkbox works

I'm using a macro to loop through a predetermined range of cells, which will then place a checkbox on said cells, linking them to the cell beneath. I'm using OnAction to add a macro to each checkbox as it is ticked. It passes 3 parameters to the macro when it is checked, the current cell row and column, and the sheet name where the checkbox was ticked.

It look like this :

'Declares an integer named i.
Dim i As Integer

'Start of for loop which will run from the lower bound of eAndAs to the upper bound.
'For i = LBound(eAndAs) To UBound(eAndAs)
For i = LBound(legacy_Array) To UBound(legacy_Array)

    'Inserts a row at the specified location, the current row + 1 + the value of i (0 to 18 depending on which run of the loop it is currently on).
    workSource.Rows(rowPos + 1 + i).Insert

    'Sets cb as equal to the specified cell in the newly inserted row.
    Set cb = workSource.CheckBoxes.Add(Cells(rowPos + 1 + i, colPos + 1).Left, Cells(rowPos + 1 + i, colPos + 1).Top, _
    Cells(rowPos + 1 + i, colPos + 1).Width, Cells(rowPos + 1 + i, colPos + 1).Height)

    'Start of With which sets the attributes of cb.
    With cb

        'Sets the caption as the current element of eAndAs.
        '.Caption = eAndAs(i)
        .Caption = legacy_Array(i)
        'Links the checkbox with the cell directly beneath it.
        .LinkedCell = Cells(rowPos + 1 + i, colPos + 1).Address
        'Adds a macro which will be activated when it is clicked. The cell's row and column position will be passed as parameters to the macro.
        '.OnAction = "'ProcessCheckBox " & colPos + 1 & "," & rowPos + 1 + i & "'"
        .OnAction = "'ProcessCheckBox " & rowPos + 1 & "," & colPos + 1 + i & ",""" & sheetName & """'"

    'End of With.
    End With

'Starts next run of loop and increments i.
Next i
---------------------------------------------------------------------------------
'Sub to process when a checkbox has been changed.
Sub ProcessCheckBox(ByVal rowPos As Integer, ByVal colPos As Integer, ByVal sheetName As String)

'Declares a worksheet object named activeSheet.
Dim activeSheet As Worksheet
'Sets activeSheet equal to the active worksheet.
Set activeSheet = ThisWorkbook.Worksheets(sheetName)

The loop executes fine, the list of 20 or so checkboxes is on the sheet. However, only when the first checkbox is ticked does the macro execute. When I remove the string variable, and hardcode the sheet I'm using, the macro executes as it should on all checkboxes.

I don't understand why this is happening. I changed the ByVal sheetName As String to ByRef in case the first checkbox created somehow took the string value, and since it was empty, the other checkboxes don't have any value to reference as a worksheet.

Any help would be appreciated. Thank you.




Aucun commentaire:

Enregistrer un commentaire