Code Screw-up - Hide will not UnHide

[yevlar]

Okay, I have a worksheet where Column F contains Check Boxes. At the top of the worksheet I have a checkbox that I want to use to hide rows that have the box in Column F checked.

I tried out the following code and applied it to a regular Form checkbox (not Active-X), and it works great to hide the rows, but will not unhide them when unchecked.

I'm assuming I either missed something in the code or am trying to use code that shouldn't be used for this purpose.

Sub hide()
Dim Rng As Range, cell As Range

Set Rng = Range("F3:F1000")

For Each cell In Rng
    Select Case cell.Value
        Case Is = True
            cell.EntireRow.Hidden = True
        Case Else
            cell.EntireRow.Hidden = False
    End Select
Next cell

End Sub

 

[Von Pookie]

The only reason the rows hid in the first place (and why the rows are not unhiding) is because you are checking the value of the *cells*, not the checkboxes.

The checkboxes may appear to be in the cells, but they are separate controls.

 

[yevlar]

I figured that checking the box would turn on the macro and unchecking would turn it off, thereby unhiding everything.

How would I go about that?

 

[yevlar]

Sorry if that seems like a stupid question. I'm still more-or-less an infant when it comes to VBA.

 

[Von Pookie]

I figured that checking the box would turn on the macro and unchecking would turn it off, thereby unhiding everything.

You can have a macro run when the checkbox is clicked, but whether it does what you want depends on the coding

Though now that I think about it further, I suppose if you have the checkboxes linked to the cells they are in--so when you check/uncheck a box the cell value changes to True/False accordingly--you could use your current code to check the value of the cells. Otherwise, you're looking at going through the range and coding to look at the *checkboxes* you created in column F, not the cells.

 

[yevlar]

I do have the cell linking on - each checkbox is linked to the cell it sits atop, and the code is looking for the true/false content therein. But I can't figure out how to have the one master checkbox at the top of the page unhide all of those rows when it is false.

 

[Von Pookie]

I do have the cell linking on - each checkbox is linked to the cell it sits atop, and the code is looking for the true/false content therein.

Right, this is an easy fix, then

One quick question, though--is the "Master" checkbox linked to a cell? If so, what cell? If it is not linked to a cell, that will work just as well. It just takes different coding.

 

[Von Pookie]

At any rate, I had some time so I just put together the two different versions of code.

If the Master checkbox IS NOT linked to a cell, this should work (note: Change the "Check Box 1" name in the code to whatever the name of your "master" checkbox is (if you select the checkbox, the name should appar in the name box above column A):

Sub hide()
Dim Rng As Range, cell As Range, cbMaster As CheckBox

'the range you want to check
Set Rng = Range("F3:F1000")

'set the master checkbox to a variable
Set cbMaster = ActiveSheet.CheckBoxes("Check Box 1")

'If the master checkbox is "true" (checked)
If cbMaster.Value = xlOn Then

    Application.ScreenUpdating = False
    'loop through the range and hide/unhide rows accordingly
    For Each cell In Rng

        'the true/false of the hidden property is set
        'according to the true/false value of the cell
        cell.EntireRow.Hidden = cell.Value
    Next cell

Else 'if master checkbox is false (unchecked)

    'unhide all rows within the range
    Rng.EntireRow.Hidden = False
End If

Application.ScreenUpdating = True

End Sub

If the Master checkbox IS linked to a cell (note: change the reference to B4 to whatever cell you have linked):

Sub hide2()
Dim Rng As Range, cell As Range

'the range you want to check
Set Rng = Range("F3:F1000")

'If the cell the master checkbox is linked to = "true" (checked)
If Range("B4").Value Then

    Application.ScreenUpdating = False
    'loop through the range and hide/unhide rows accordingly
    For Each cell In Rng

        'the true/false of the hidden property is set
        'according to the true/false value of the cell
        cell.EntireRow.Hidden = cell.Value
    Next cell

Else 'if master checkbox is false (unchecked)

    'unhide all rows within the range
    Rng.EntireRow.Hidden = False
End If

Application.ScreenUpdating = True

End Sub

Once you get the code in place, just right-click the checkbox, select "Assign macro" and choose your macro from the list. Click OK.

When you click the master checkbox, the macro you assigned run.

 

[yevlar]

The second set of code is succeeding to hide and unhide the rows, but each time I click the master checkbox I get Error 400.

 

[yevlar]

Hmmm... idea.....

One of the other things I'm doing is conditional formatting so when the checkbox in Column F is True, the entire row switches to a Green background color. Would it be easier to have the Master Checkbox hide all rows that are Green instead of messing with the checkboxes themselves?