VBA Cycle through visible checkboxes and hide if unchecked

[Gti182]

Hi All,

I'm looking for some code to cycle through the visible checkboxes in a sheet and only hide those which are not ticked.

What adds a level of complexity is some checkboxes may already be hidden

 

[gallen]

That's a strange one as once they are unchecked, how will they ever become visible again if you need to check them?

 

[gallen]

This will do as you ask any way:

Sub SetCheckboxes()
    Dim cb As CheckBox
    
    For Each cb In ActiveSheet.CheckBoxes
        cb.Visible = cb.Value = 1
    Next
    
End Sub

 

[Gti182]

Not really an issue as I intend to add the hide visible checkboxes code to a separate send to pdf bit of code I already have in place. The send to PDF code will only be executed by the user once they are happy with their checkbox selections.

 

[Gti182]

This seems to unhide checkboxes that were already hidden.

For my example. I have 20 checkboxes, if a scenario A is selected 10 of these are hidden. For the remaining 10 checkboxes which are visible lets say 6 are ticked, i'd like to hide just those 4 unticked ones.

 

[Gti182]

in my head the code would go something like this but can't quite figure out the exact syntax

For each visible checkbox between i to 20.
If checkbox(i).value = 0 'if unticked
then checkbox.visible = False
End if
Next

 

[gallen]

in my head the code would go something like this but can't quite figure out the exact syntax

For each visible checkbox between i to 20.
If checkbox(i).value = 0 'if unticked
then checkbox.visible = False
End if
Next

[COLOR=#333333]Sub SetCheckboxes()[/COLOR]    Dim cb As CheckBox
    
    For Each cb In ActiveSheet.CheckBoxes
        if cb.visible = true then cb.Visible = cb.Value = 1
    Next

[COLOR=#333333]End Sub[/COLOR]

 

[Gti182]

That works great thanks gallen!

 

[Rick Rothstein]

[COLOR=#333333]Sub SetCheckboxes()[/COLOR]    Dim cb As CheckBox
    
    For Each cb In ActiveSheet.CheckBoxes
        [B][COLOR="#FF0000"]if cb.visible = true then cb.Visible = cb.Value = 1[/COLOR][/B]
    Next

[COLOR=#333333]End Sub[/COLOR]

You can also write the highlighted line of code this way...

cb.Visible = cb.Value * cb.Visible = -1

 

[gallen]

That works great thanks gallen!

You're very welcome.