Using form control checkboxes to clear ranges on other pages- VBA Excel

[georgedixon]

Test Clear.xls
Hi,
I have set up a test sheet to demonstrate what I am trying to do. I have a "Summary" page, which includes checkboxes with the names of subsequent worksheets. On these other worksheets, there are lists where a user can write "Y" next to any to indicate that they want this item. I want this list to be fairly easy to clear, so on the summary page I was looking for the user to be able to clear the Y's on any page, by deselecting it from the list.

The Y values are meant to clear upon click of a button, the code for which is as follows:

Private Sub CommandButton1_Click()
Dim LastRowH As Integer
Dim ClearContent As Boolean
Dim ws As Worksheet






    For Each ws In ActiveWorkbook.Worksheets
        ClearContent = False
      
        
        LastRowH = ws.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row
        
    
    For Each CheckBox In Sheets("Summary").CheckBoxes
        If CheckBox.Caption = ws.Name Then
            If CheckBox.Value = False Then
                ClearContent = True
            End If
        End If
        
    Next CheckBox
    
        If ClearContent = True Then
            For c = 1 To LastRowH   'if not checked, looks on current worksheet (within loop) and if any "Y" or "y" vals, clears them
                If ws.Range("H" & c).Value = "Y" Or ws.Range("H" & c).Value = "y" Then
                    ws.Range("H" & c).ClearContents
                End If
            Next
        End If
   
    Next
End Sub

I have also included the test spreadsheet as an attachment so you can see more clearly the function of it.

[mikerickson]

You might try something like

For Each oneShape in Sheets("Summary).Shapes
    If oneShape.Caption = ws.Name Then
        If oneShape.Type = msoFormControl Then
            If oneShape.FormControlType = xlCheckBox Then
                ClearContent = True
                Exit For
            End If
        End If
    End If
Next oneShape

If ClearContent Then
    With ws.Range("H:H")
        For i = 1 to .Cells(Rows.Count, 1).End(xlUp).Row
            With .Cells(i, 1)
                If LCase(.Text) = "y" Then .ClearContents
            End With
        Next i
    End With
End If

[p45cal]

try changing:
If CheckBox.Value = False Then
to:
If CheckBox.Value = xlOff Then

You could also reduce your looping a bit:

Sub CommandButton1_Click()
Dim ws As Worksheet, CheckBox, c

For Each CheckBox In CheckBoxes
  If CheckBox = xlOff Then
    Set ws = Nothing
    On Error Resume Next
    Set ws = Sheets(CheckBox.Caption)
    On Error GoTo 0
    If Not ws Is Nothing Then
      For c = 1 To ws.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row    'if not checked, looks on current worksheet (within loop) and if any "Y" or "y" vals, clears them
        If Application.Trim(UCase(ws.Range("H" & c).Value)) = "Y" Then ws.Range("H" & c).ClearContents 'caters for upper and lower case and leading/trailing spaces.
      Next
    End If
  End If
Next CheckBox
End Sub

[georgedixon]

try changing:
If CheckBox.Value = False Then
to:
If CheckBox.Value = xlOff Then

You could also reduce your looping a bit:

Sub CommandButton1_Click()
Dim ws As Worksheet, CheckBox, c

For Each CheckBox In CheckBoxes
  If CheckBox = xlOff Then
    Set ws = Nothing
    On Error Resume Next
    Set ws = Sheets(CheckBox.Caption)
    On Error GoTo 0
    If Not ws Is Nothing Then
      For c = 1 To ws.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row    'if not checked, looks on current worksheet (within loop) and if any "Y" or "y" vals, clears them
        If Application.Trim(UCase(ws.Range("H" & c).Value)) = "Y" Then ws.Range("H" & c).ClearContents 'caters for uper and lower case and leading/trailing spaces.
      Next
    End If
  End If
Next CheckBox
End Sub

Thank you very much, works perfectly