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

[georgedixon]

22844
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 :)