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.
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.