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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

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.

Forum statistics

Threads
1,141,227
Messages
5,705,141
Members
421,380
Latest member
Nuwan Sanjeewa Aponso

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