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
No. That is the macro you want to call. I want to know how you are calling it.
Oh apologies! I simply assigned the macro to the checkbox by right clicking on the checkbox and chose Assign Macro.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,358
Office Version
  1. 2010
Platform
  1. Windows
Interesting. My ActiveX checkboxes (Excel 2010) don't have an Assign Macro choice, they have a View Code choice, which doesn't follow the checkboxes if they are copied by dragging.
 

SB_Excel

New Member
Joined
Nov 22, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Interesting. My ActiveX checkboxes (Excel 2010) don't have an Assign Macro choice, they have a View Code choice, which doesn't follow the checkboxes if they are copied by dragging.
Well now you having me wondering if I inserted them using the Form Controls option instead. It has been weeks since I inserted them so I could be mistaken - and back then I didn't realize that there was a difference between the two. But if I am mistaken, would that affect how the code is working when called? As noted above when debugging it does work, but then once it completes it's almost as if it erases the results of the code it just ran.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,358
Office Version
  1. 2010
Platform
  1. Windows
would that affect how the code is working when called?
Yes, but why call a sub to loop though 200+ checkboxes when you know which row needs to be dealt with?

I would try this for the change event in column "o"
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr As Long
'limit to single cell
If Target.CountLarge > 1 Then Exit Sub
'limit to range of column "o"
If Not Intersect(Target, Range("o12:o237")) Is Nothing Then
    If Range("o" & Target.Row).Value = "Required" Then
        Worksheets("Housing Corps").CheckBoxes("Check Box R" & Target.Row).Visible = True
    Else
        Worksheets("Housing Corps").CheckBoxes("Check Box R" & Target.Row).Visible = False
    End If
End If
End Sub

And for the click of the check boxes
I'd change the name of your original code, so it isn't the macro being called
VBA Code:
Sub Original_HideCheckBoxesHC()
Dim i As Long
For i = 1 To 24
    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
End Sub
and use this for the macro that is being called
VBA Code:
Sub HideCheckBoxesHC()
    Dim WhoCalled As String
    Dim CallingCell As Range

WhoCalled = Application.Caller
    'MsgBox WhoCalled
Set CallingCell = Range(Split(WhoCalled, " ")(2))
    'MsgBox CallingCell.Address
If CallingCell.Value = "Required" Then
    Worksheets("Housing Corps").CheckBoxes(WhoCalled).Visible = True
Else
    Worksheets("Housing Corps").CheckBoxes(WhoCalled).Visible = False
End If

End Sub

Just my 2¢ worth, hope it helps
 

SB_Excel

New Member
Joined
Nov 22, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes, but why call a sub to loop though 200+ checkboxes when you know which row needs to be dealt with?

I would try this for the change event in column "o"
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr As Long
'limit to single cell
If Target.CountLarge > 1 Then Exit Sub
'limit to range of column "o"
If Not Intersect(Target, Range("o12:o237")) Is Nothing Then
    If Range("o" & Target.Row).Value = "Required" Then
        Worksheets("Housing Corps").CheckBoxes("Check Box R" & Target.Row).Visible = True
    Else
        Worksheets("Housing Corps").CheckBoxes("Check Box R" & Target.Row).Visible = False
    End If
End If
End Sub

And for the click of the check boxes
I'd change the name of your original code, so it isn't the macro being called
VBA Code:
Sub Original_HideCheckBoxesHC()
Dim i As Long
For i = 1 To 24
    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
End Sub
and use this for the macro that is being called
VBA Code:
Sub HideCheckBoxesHC()
    Dim WhoCalled As String
    Dim CallingCell As Range

WhoCalled = Application.Caller
    'MsgBox WhoCalled
Set CallingCell = Range(Split(WhoCalled, " ")(2))
    'MsgBox CallingCell.Address
If CallingCell.Value = "Required" Then
    Worksheets("Housing Corps").CheckBoxes(WhoCalled).Visible = True
Else
    Worksheets("Housing Corps").CheckBoxes(WhoCalled).Visible = False
End If

End Sub

Just my 2¢ worth, hope it helps

Thank you for trying, but neither of those did the trick.

The change event in column O will show all checkboxes again except the target row value. I would like all checkboxes to remain hidden even if there is a change in the target row.

Changing the HideCheckBoxesHC code as per above and then selecting any checkbox still seems to show all the checkboxes again.
 

SB_Excel

New Member
Joined
Nov 22, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Thank you for trying, but neither of those did the trick.

The change event in column O will show all checkboxes again except the target row value. I would like all checkboxes to remain hidden even if there is a change in the target row.

Changing the HideCheckBoxesHC code as per above and then selecting any checkbox still seems to show all the checkboxes again.
Update:

Not trying to call the code in Private Sub Worksheet_Change(ByVal Target As Range) but simply pasting my code from the module did the trick relating to the change in value in column O. So thank you for pointing me in the right direction!

I'm still working on debugging the checkbox selection issue and will let you know if I figure out a solution.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,358
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I can't see there being that much trouble to do what you're trying to do.
Can you share a sanitized copy of that spreadsheet so we are both working with the same things.
I use the free portion of the file sharing site box.com with no issues.
 

SB_Excel

New Member
Joined
Nov 22, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I can't see there being that much trouble to do what you're trying to do.
Can you share a sanitized copy of that spreadsheet so we are both working with the same things.
I use the free portion of the file sharing site box.com with no issues.
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!
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,358
Office Version
  1. 2010
Platform
  1. Windows
Glad you got it sorted.

Another 2¢ worth...
This is a macro I tucked away years ago and always find myself editing and using
for insertion of Form control check boxes on a sheet.
Maybe it will be of some use or maybe not.
VBA Code:
Private Sub Insert_Form_Checkboxes()
     Dim myCell As Range, myRng As Range
     Dim CBX As CheckBox

With ActiveSheet
    'delete ALL existing checkboxes from sheet, LINKS ARE NOT CLEARED.
    '.CheckBoxes.Delete  '<~~~~~ comment out to NOT delete existing checkboxes
    'Exit Sub           '<~~~~~ uncomment when deleting checkboxes only.

    Set myRng = .Range("A2:A20")    '<~~~~~ enter the range to have checkboxes
End With
Application.ScreenUpdating = False
    For Each myCell In myRng.Cells
        With myCell
            Set CBX = .Parent.CheckBoxes.Add _
                        (Top:=.Top, _
                         Left:=.Left, _
                         Width:=.Width, _
                         Height:=.Height)           'makes click area same size as cell
                         
            CBX.Name = "CBX_" & .Address(0, 0)
            CBX.Caption = "Label goes here"         'whatever you want, "" for none
            CBX.Value = xlOff                       'initial value unchecked
            CBX.LinkedCell = .Offset(0, 1).Address  '<~~~~~ offset to linked cell
            CBX.OnAction = "Macro_to_call"          'macro to run each time clicked
        End With
    Next myCell
Application.ScreenUpdating = True
End Sub
 

SB_Excel

New Member
Joined
Nov 22, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Glad you got it sorted.

Another 2¢ worth...
This is a macro I tucked away years ago and always find myself editing and using
for insertion of Form control check boxes on a sheet.
Maybe it will be of some use or maybe not.
VBA Code:
Private Sub Insert_Form_Checkboxes()
     Dim myCell As Range, myRng As Range
     Dim CBX As CheckBox

With ActiveSheet
    'delete ALL existing checkboxes from sheet, LINKS ARE NOT CLEARED.
    '.CheckBoxes.Delete  '<~~~~~ comment out to NOT delete existing checkboxes
    'Exit Sub           '<~~~~~ uncomment when deleting checkboxes only.

    Set myRng = .Range("A2:A20")    '<~~~~~ enter the range to have checkboxes
End With
Application.ScreenUpdating = False
    For Each myCell In myRng.Cells
        With myCell
            Set CBX = .Parent.CheckBoxes.Add _
                        (Top:=.Top, _
                         Left:=.Left, _
                         Width:=.Width, _
                         Height:=.Height)           'makes click area same size as cell
                        
            CBX.Name = "CBX_" & .Address(0, 0)
            CBX.Caption = "Label goes here"         'whatever you want, "" for none
            CBX.Value = xlOff                       'initial value unchecked
            CBX.LinkedCell = .Offset(0, 1).Address  '<~~~~~ offset to linked cell
            CBX.OnAction = "Macro_to_call"          'macro to run each time clicked
        End With
    Next myCell
Application.ScreenUpdating = True
End Sub
Thank you for the tip! I'll keep this in mind for the next time. :)
 
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,569
Messages
5,770,908
Members
425,651
Latest member
Mark Cashin

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