Excel Macro Enabled Worksheet - How to clear grouped checkboxes in a specific row and column without clearing all checkboxes in worksheet?

Question

Current Issue: I need help understanding how I can clear a specific row and columns in that row, that contain grouped form control checkboxes.

I am copying a row and shifting it down to the next row. I'm currently using ClearContents to clear the text copied into the new row in columns A, B and D. But this doesn't work to clear the grouped form control checkboxes copied into columns C and E.

I want to be able to clear those checkboxes in the newly copied row so that a user is presented with a clean row, that they can enter text in and make selections in checkboxes without old selections being there.

How the code is currently working: The below code is tied to the button 'Add a New Row', when this button is clicked it presents a user prompt asking the user to enter the row number of the new row to add - example of user entering a new row number, to copy row 14 down into row 15

In the newly copied row, columns A, B and D are cleared of text - ready for a user to add a new entry, which is currently working well: showing new row copied from row 14 into row 15 and clearing columns A, B and D

I also want columns C and E in this new row to have all the checkboxes cleared...they are currently just being copied from row 14 down as is with the checkboxes ticked that are selected in row 14. These checkboxes are grouped, e.g. column C checkboxes grouped as one group, column E checkboxes grouped as another group.

Is there a way I can target a specific row/column and clear the checkboxes, and do this without clearing all the checkboxes in other rows?

Important - what I am wanting to achieve:

I want to try and understand how I can add any new code to the code I already have under the Add a New Row button code below.
Row 14 is already populated (as this will be a template), so the user should add from row 15 onward. This may not be the case in other worksheets, so hopefully I can just update the row that I want to use as the template row in other worksheets.
Ideally, if there was a way to update my code to throw an error if a user tries to copy rows 14 or above, that would be better. Currently, if a user selects row 14, it copies my headings row and inserts this again, which I don't want to happen:

showing headings copied again if the user tries to copy row 14

Add a New Row Button code:

Private Sub CommandButton1_Click()

    Dim rowNum As Long
    On Error Resume Next
    rowNum = Application.InputBox(Prompt:="Enter Row Number of New Row to Add:", _
        Title:="Add New Row", Type:=1)
    Rows(rowNum).Insert Shift:=xlDown
    If Err.Number > 0 Then GoTo errH
    
    Range("A" & rowNum - 1).Resize(, 1).Copy Range("A" & rowNum)
    Range("B" & rowNum - 1).Resize(, 1).Copy Range("B" & rowNum)
    Range("C" & rowNum - 1).Resize(, 1).Copy Range("C" & rowNum)
    Range("D" & rowNum - 1).Resize(, 1).Copy Range("D" & rowNum)
    Range("E" & rowNum - 1).Resize(, 1).Copy Range("E" & rowNum)
    Range("F" & rowNum - 1).Resize(, 1).Copy Range("F" & rowNum)
        
    Range("A" & rowNum - 0).ClearContents
    Range("B" & rowNum - 0).ClearContents
    Range("D" & rowNum - 0).ClearContents

errH:
End Sub

Any help would be greatly appreciated. Thanks

Please Note: I've looked online at a number of different bits of code that kind of do something similar, but most seem to uncheck all the checkboxes on a worksheet and I don't have the coding knowledge to write/repurpose that code to suit what I need.

ClearContents doesn't work to target the form control checkboxes, and Clear removes the underlying formatting of a column/row, which I don't want.

I had a look online but kept getting confused with how others' code was working.

I repurposed someone elses code I found here.

What I'm trying currently:

' New Code:
' NOTE: Not sure how to link this to the CommandButton1_Click(1) below...
Sub setFormCheckboxes(ws As Worksheet, row As Long, value As Boolean)
    Dim cb As CheckBox
    For Each cb In ws.CheckBoxes
        If cb.TopLeftCell.row = row Then cb.value = value
    Next
End Sub

' Original Code + new code to Copy Row and Set Form Control Checknoxes to False
Private Sub CommandButton1_Click()
    
    Dim rowNum As Long
    On Error Resume Next
    
    ' User prompt to ask user to enter the row number of the next blank row, so the previous row is shifted/copied down
    rowNum = Application.InputBox(Prompt:="Enter the row number of the blank row below your last entry:", _
        Title:="Add New Row", Type:=1)
    Rows(rowNum).Insert Shift:=xlDown
    If Err.Number > 0 Then GoTo errH
      
    ' New Code: Copy Row from columns A-F
    ' NOTE: This is working well - thanks
    Range("A" & rowNum - 1).Resize(, 6).Copy Range("A" & rowNum)
    
    ' Clear text from columns A, B and D in newly copied row
    Range("A" & rowNum - 0).ClearContents
    Range("B" & rowNum - 0).ClearContents
    Range("D" & rowNum - 0).ClearContents
    
    ' New Code: Set Form Control Checkbxes to false
    ' NOTE: This isn't working to clear any of the checkboxes in my newly copied row yet...
    setFormCheckboxes ActiveSheet, rowNum, False
   
errH:
End Sub

Trying to use FunThomas's code and merge with my own - not working

@FunThomas I've edited my post to include what I'm currently trying to do with your code merged into my own, which isn't currently throwing any errors, but also not clearing the checkboxes in my newly copied row... — Amy Chong, Jul 18 '23 at 22:10

FunThomas · Answer 1 · 2023-07-18T12:46:30.363

You can identify the row of a checkbox (basically of any shape) using the TopLeftCell property of the shape.

So all you have to do is to loop over all checkboxes of the sheet and check the row. You just need to know if you are using Form Controls or ActiveX controls. If you are not familiar with that, have a look to Differences between Excel's Form Controls & ActiveX Controls

For Form controls, this is rather easy as every worksheet has a CheckBoxes-collections. The following routine sets or resets all checkboxes on a row of a worksheet:

Sub setFormCheckboxes(ws As Worksheet, row As Long, value As Boolean)
    Dim cb As CheckBox
    For Each cb In ws.CheckBoxes
        If cb.TopLeftCell.row = row Then cb.value = value
    Next
End Sub

For ActiveX controls, no such collection exists, so you need to loop over all shapes and check if we have an ActiveX Checkbox.

Sub setActiveXCheckboxes(ws As Worksheet, row As Long, value As Boolean)
    Dim cb As Shape
    For Each cb In ThisWorkbook.Sheets(2).Shapes
        If cb.Type = msoOLEControlObject Then
            If TypeName(cb.DrawingObject.Object) = "CheckBox" Then
                If cb.TopLeftCell.row = row Then cb.DrawingObject.Object.value = value
            End If
        End If
    Next
End Sub

Btw: You can copy all (6) cells of the row at once:

' Copy row
Range("A" & rowNum - 1).Resize(, 6).Copy Range("A" & rowNum)
' Reset all checkBoxes, depending on which controls types you use:
setFormCheckboxes ActiveSheet, rowNum, False
setActiveXCheckboxes ActiveSheet, rowNum, True

Thanks for reply, I'm using form control checkboxes. I'll have a go trying to add this into my code and see if I can get it to work and report back. I want it to fit into the code added toward the bottom of my original question... — Amy Chong, Jul 18 '23 at 11:51
Will this work on grouped form control checkboxes? Column C has 4x form control checkboxes in a group, column E has 6 grouped form control checkboxes. It's currently not working for me, but I'm not sure how to structure your code with mine in order for your bit to be called/referenced correctly. Have been playing around with it, but can't get it right/keep getting compile errors. Have also tried adding your code to a module and referencing the module from my private sub. — Amy Chong, Jul 18 '23 at 23:13

Excel Macro Enabled Worksheet - How to clear grouped checkboxes in a specific row and column without clearing all checkboxes in worksheet?

1 Answers1