Hidden Checkboxes Reappear When Any Checkbox on Sheet is Selected

[SB_Excel]

Hi there,

Apologies up front on etiquette, this is my first time posting to any excel forum and my VBA knowledge is limited. But I have a two-part problem I'm hoping someone can help solve.

Part 1
I have a number of checkboxes in column R from rows 12 to 237. I have included a marco called "HideCheckBoxesHC" in a separate module to hide the checkboxes included in each of the cells where the criteria "Required" is not met. This VBA code is as follows:

Dim i As Long
For i = 1 To 226
If Range("R" & i + 11).Value = "Required" Then
Worksheets("Housing Corps").CheckBoxes("Check Box R" & i + 11).Visible = True
Else
Worksheets("Housing Corps").CheckBoxes("Check Box R" & i + 11).Visible = False
End If
Next i

The cells from R12:R237 use an if statement to populate "Required" only when cells in the same row but in column O contain the letter "a".

After running this macro once, any changes to column O (regardless of value) appear to reset all checkboxes.visible values to True as all are showing again. Therefore, I included the following code in the relevant worksheet:

'Private Sub Worksheet_Change(ByVal Target As Range)
'Application.ScreenUpdating = False
'If Not Intersect(Target, Range("O12:O237")) Is Nothing Then
'HideCheckBoxesHC
'End If
'Application.ScreenUpdating = True
'End Sub

This seems to only sometimes do the trick when adding any value to a cell between O12 and O237 (which I can't seem to narrow down why it's working when it does), but it works 100% of the time when I remove any value from cells O12:237.

Where am I going wrong to ensure that no matter what the value added or deleted in column O, the macro will run as intended: to hide any checkboxes where the cell value is not "Required"?


Part 2
Further to the above mystery, the second problem I'm having is anytime a check box is selected or unselected, all checkboxes are visible again. I have tried assigning the Macro"HideCheckBoxesHC" to the checkboxes themselves, but after the checkbox is selected and the macro runs, all checkboxes are still visible.

Please let me know if you need further information to assist.

 

[SB_Excel]

Well I must apologize for wasting your time, but when trying to sanitize my workbook I believe I found my issue. The reason why the code was working until it exited the sub is because afterwords it was running another code that I hadn't picked up on. I had a public sub that I created weeks ago that was repositioning the checkboxes in the center of the cell horizontally. While I had believed it would only affect the visible checkboxes, I believe it was actually making all the checkboxes visible again before aligning them. I deleted that code and the issue doesn't appear to occur anymore.

I really appreciate your help and did pick up a few new tricks form this thread. So thank you both very much!

Well, two steps forward and 5 steps back it would appear. I can't seem to get both of these codes to work together so that it will both hide the checkboxes based on my criteria but also address the checkbox placement issue when any row is filtered. The code that I was referring to that was affecting the functionality of my HideCheckBoxesHC code is a very common code I have found when searching how to keep shapes that are in rows that have been filtered from displaying:

Private Sub Worksheet_Calculate()
    Dim shp As Shape

    For Each shp In Me.Shapes
        shp.Visible = shp.TopLeftCell.EntireRow.Height <> 0
    Next
End Sub

Is there any iteration of this code that I can use with my HideCheckBoxesHC code to get both functions to work simultaneously?