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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,795
Hi SB,
welcome to MrExcel.

First things first: when you post code put it between code tags. See the example at the bottom of my post. It is easy: click the little VBA icon in your post window, and paste your code.

Second, these intermittant issues are often difficult to replicate on another PC without the same workbook. But since you are a starting VBA programmer, I'll show you a tool that will make your life a lot easier: the VBA debugger.
You can also read about it in more depth, plus some other handy coding in my little guide, link below.

In the VBA editor there are a few things to help you finding problems.

One is the breakpoint.
  • If you click on the margin just left of any code line, the line will turn dark red. (clicking it again will turn it off)
  • Now if you run code, the code will pause at this line. The line will be shown in yellow highlight.
  • You can use this to see if the code comes to this point (is it run at all)
  • But you can now also use some other debugging tools.
Two is the step through code. F8 key
  • Pressing the F8 key will make VBA go through the code one line at a time.
  • This will help to see if it is following the expected route (with If/ThEN/ELSE, etc)
Three is the mouse hover to check on values of your variables.
  • Hold the mousepointer over a variable, and its value will appear.
Four is to move the line to be executed to a different point
  • the little yellow arrow in front of the line to be executed can be dragged up or down with the current sub or function. For instance to rerun a section after you made a correction to the code

Five is the edit code
  • While the code is paused or stepped through, you can make changes, add a variable and some code, etc.
Run the code F5
  • pressing the F5 key will run the code from the current point until the end or the next breakpoint
There is a lot more about debugging. Read up on the 'immediate window' for instance

So give this a try in your workbook, to see why it sometimes does not work
 

SB_Excel

New Member
Joined
Nov 22, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi SB,
welcome to MrExcel.

First things first: when you post code put it between code tags. See the example at the bottom of my post. It is easy: click the little VBA icon in your post window, and paste your code.

Second, these intermittant issues are often difficult to replicate on another PC without the same workbook. But since you are a starting VBA programmer, I'll show you a tool that will make your life a lot easier: the VBA debugger.
You can also read about it in more depth, plus some other handy coding in my little guide, link below.

In the VBA editor there are a few things to help you finding problems.

One is the breakpoint.
  • If you click on the margin just left of any code line, the line will turn dark red. (clicking it again will turn it off)
  • Now if you run code, the code will pause at this line. The line will be shown in yellow highlight.
  • You can use this to see if the code comes to this point (is it run at all)
  • But you can now also use some other debugging tools.
Two is the step through code. F8 key
  • Pressing the F8 key will make VBA go through the code one line at a time.
  • This will help to see if it is following the expected route (with If/ThEN/ELSE, etc)
Three is the mouse hover to check on values of your variables.
  • Hold the mousepointer over a variable, and its value will appear.
Four is to move the line to be executed to a different point
  • the little yellow arrow in front of the line to be executed can be dragged up or down with the current sub or function. For instance to rerun a section after you made a correction to the code

Five is the edit code
  • While the code is paused or stepped through, you can make changes, add a variable and some code, etc.
Run the code F5
  • pressing the F5 key will run the code from the current point until the end or the next breakpoint
There is a lot more about debugging. Read up on the 'immediate window' for instance

So give this a try in your workbook, to see why it sometimes does not work
Thank you so much for your reply. I'm very familiar with the step through using the F8 key, but the other items are very useful as I knew they were tools under the Debug menu, but I really didn't understand how and when to use them.

The tricky issue that I'm having is that my code works like a dream when I run it through the visual basics window or when I use the keyboard short cut that I assigned to the code. But what I'm having trouble with is getting it to run when I select a check box (the macro has been assigned to every checkbox) or when I change the value of a cell in Column O (I have the code triggered to run when a cell value changes in this column through Worksheet_Change (ByVal Target as Range).

For instance, if all the checkboxes that I want to hide are hidden, and I click a checkbox, all the hidden checkboxes reappear. Also, in some instances, when I change a cell value in column O, it will show all the previously hidden checkboxes as well. I just can't determine why some cell changes in column O run the code perfectly and some changes result in the reappearance of the checkboxes.

So it's not so much an issue with the code as an issue with why it won't run when called to do so, which I'm not sure how to debug that.

I've attached two images below to provide some more context. The first image shows columns R and S with no checkboxes as the cell value is not "Required". I manually ran my code to get this result. Then the second image is what happens when I check any checkbox - all the hidden checkboxes reappear. But when I use my short cut keys to run the code again afterwards, the unwanted checkboxes will hide again.
 

Attachments

  • Capture.JPG
    Capture.JPG
    37.1 KB · Views: 8
  • Capture2.JPG
    Capture2.JPG
    47.1 KB · Views: 8

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,348
Office Version
  1. 2010
Platform
  1. Windows
How did you insert the check boxes?
If you open the Selection pane under Find & Select do you have any check boxes with the same names?
 

SB_Excel

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

ADVERTISEMENT

How did you insert the check boxes?
If you open the Selection pane under Find & Select do you have any check boxes with the same names?
Hi there,

The first checkbox was inserted using insert ActiveX controls on the Developer ribbon, then I simply copied the checkboxes to the other cells. They did all have the same name originally, but then I inserted and ran a VBA code to rename them all based on their cell location. For instance the first box is named R12, the second one to the right is S12 and so on and now they all have unique names.
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,795
I still suggest you set a breakpoint in HideCheckBoxesHC sub. Then go back to your excel and tick, untick boxes. And then step through to see how it behaves.

Same for adding information in column O.
Debugging can be a lot of work...
 

SB_Excel

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

ADVERTISEMENT

I still suggest you set a breakpoint in HideCheckBoxesHC sub. Then go back to your excel and tick, untick boxes. And then step through to see how it behaves.

Same for adding information in column O.
Debugging can be a lot of work...
Thank you so much, that certainly did the trick to narrow down my issue on both the checkbox and the cell value change - appears to be the same issue: the code works as intended all the way through until the "End Sub" line. At that point they all repopulate. Do you have any insights as to why it would behave this way? I've restated the code below for your convenience.


VBA Code:
Sub HideCheckBoxesHC()
    'Hide checkboxes if not required
    Dim i As Long
    For i = 1 To 115
        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
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,348
Office Version
  1. 2010
Platform
  1. Windows
Can you post the code you are using to call the HideCheckBoxesHC macro when the check boxes are clicked
 

SB_Excel

New Member
Joined
Nov 22, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Can you post the code you are using to call the HideCheckBoxesHC macro when the check boxes are clicked
Here you go! My last reply to sijpie may provide some context.

VBA Code:
Sub HideCheckBoxesHC()
    'Hide checkboxes if not required
    Dim i As Long
    For i = 1 To 115
        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
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,348
Office Version
  1. 2010
Platform
  1. Windows
No. That is the macro you want to call. I want to know how you are calling it.
 
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,151,780
Messages
5,766,425
Members
425,353
Latest member
ExcelRalph

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