georgedixon
09-07-2018, 05:30 AM
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.
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.