Code Screw-up - Hide will not UnHide

Status
Not open for further replies.

yevlar

New Member
Joined
Apr 23, 2005
Messages
41
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.

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

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

New Member
Joined
Apr 23, 2005
Messages
41
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

New Member
Joined
Apr 23, 2005
Messages
41
Sorry if that seems like a stupid question. I'm still more-or-less an infant when it comes to VBA. :)
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686

ADVERTISEMENT

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

New Member
Joined
Apr 23, 2005
Messages
41
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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):
Code:
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):
Code:
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

New Member
Joined
Apr 23, 2005
Messages
41
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

New Member
Joined
Apr 23, 2005
Messages
41
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?
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,108,709
Messages
5,524,433
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top