Hidden Checkboxes Reappear When Any Checkbox on Sheet is Selected

SB_Excel

New Member
Joined
Nov 22, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 22, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,572
Messages
5,770,923
Members
425,652
Latest member
Pemby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top