Automatically Hiding rows based on checkboxes

**Jake Bierly** · 09-19-2012, 02:33 PM

Hi all!

I'm trying to figure out a way to automatically hide a number of rows based on a checkbox. Basically I want to hide rows 27:37 if a checkbox is NOT checked, and show the same rows if the checkbox is checked. I currently have the rows grouped, however I need to really tighten this workbook up and the grouping doesn't allow the clean look that I'm hoping to achieve.

Attached is a sample of what I am trying to accomplish. I would like to be able to hide rows 27:37 if the checkbox for "Land Tracts" IS NOT checked, as well as show rows 27:37 if the checkbox IS selected.

I really appreciate any help the community could offer.

Jake

**watersev** · 09-19-2012, 03:05 PM

hi Jake Bierly, welcome to Excelforum, please check attachment

Sub test()
Rows("27:37").Hidden = IIf(ActiveSheet.Shapes("Check Box 28").DrawingObject.Value = 1, 0, 1)
End Sub

**Tsjallie** · 09-19-2012, 03:51 PM

Hi Jake,
I edited your sheet to have the checkbox hide/unhide the rows. See attachment.

This what I've done:
Firstly you have to link the checkbox to a cell (I've choosen the cell rigth above the checkbox just to illustrate, but it can be anay cell on any sheet).
Secondly you will have to link the checkbox to a macro: move your mouse over the checkbox and right-click. Select Assign macro and in the next window Edit.
You will see the macro-editor with the simple code which reads the content of the linked cell (B8) and hides the rows if B8 is false, resp. shows the rows when B8 is true. I think you can copy this yourself for any other checkboxes if necessary.
You can also find the macro when you select Macros in the Extra-menu. Or you can select the Visual Basic Editor and open the Module there.

By the way, don't mind the (maybe) weird name of the macro