I recently received a question about how to hide or collapse
a row if a checkbox in that row wasn’t checked. There are a lot of good lessons
in this example. When you’re stuck on a problem, the best thing to do is to
break it down into smaller problems and solve each one at a time. Let's do it!
How to Insert Checkboxes in Excel
The first lesson is how to add a checkbox to an Excel sheet.
Go to Developer tab then Insert. There are two types of checkboxes: Form Control
and ActiveX Control. For this example, we are going to use Form Control
checkboxes because you can link these directly to a cell.
How to link a checkbox to a cell
The next lesson is how to assign a checkbox to a cell. We’re
going to do this to help with hiding the rows later. When you create the first
Form Control checkbox in cell A2, in the formula bar type “=$B$2” to assign B2
to the checkbox. Now, if the checkbox is checked B2 should read TRUE, if not
selected it should say FALSE.
**The one downside to this method is if you need 100
checkboxes it could take some time to manually assign each checkbox to a cell.
There is probably a way to automate this task, however, for simplicity of this
example we’re going to say we only need five checkboxes and assign them all
manually.**
Now your example sheet should look like this:
How to hide rows
based on a cell’s value in VBA
The hiding of all the checkboxes and rows will be done
automatically with a VBA macro. If you’re new to macros see this gettingstarted guide.
Again, break it down
into smaller problems. First, let’s figure out how to hide rows based on a
condition, the TRUE or FALSE value in our helper column.
Define the first row
that contains data we might want to hide:
BeginRow = 2
Define the last row that
might contain data we want to hide.
EndRow = 5
Define the column number
of our helper info, the column with the true or false values.
ChkCol = 2
Loop through the range
of rows we just defined and if the value of the cell in our helper column is
false, then hide that entire row:
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = False
Then
Cells(RowCnt,
ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
How to hide
checkboxes automatically with macro
Dim CB As Shape
Dim sh As Worksheet
Set sh = ActiveSheet
We need to loop through all the shapes in the active sheet,
see if they are the form control type of Check Box:
For Each CB In sh.Shapes
If CB.Type =
msoFormControl Then
If
CB.FormControlType = xlCheckBox Then
'MsgBox CB.Name,
vbOKOnly
'if check box is
checked, keep it visible, otherwise, hide it
If
CB.OLEFormat.Object.Value = 1 Then
CB.OLEFormat.Object.Visible = True
Else
CB.OLEFormat.Object.Visible = False
End If
End If
End If
Next CB
Full Macro to Hide
Rows and Checkboxes
Here’s the final code that hides entire rows based on
checkbox is checked or not.
Sub Hide_checkboxes()
Dim CB As Shape
Dim sh As Worksheet
Set sh = ActiveSheet
For Each CB In sh.Shapes
If CB.Type =
msoFormControl Then
If
CB.FormControlType = xlCheckBox Then
'MsgBox CB.Name,
vbOKOnly
'if check box is
checked, keep it visible, otherwise, hide it
If
CB.OLEFormat.Object.Value = 1 Then
CB.OLEFormat.Object.Visible = True
Else
CB.OLEFormat.Object.Visible = False
End If
End If
End If
Next CB
'now hide the rows
BeginRow = 2
EndRow = 5
ChkCol = 2
For RowCnt =
BeginRow To EndRow
If
Cells(RowCnt, ChkCol).Value = False Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub
How to unhide rows
and checkboxes in Excel
Conversely, I also made a code to reset everything and
unhide all checkboxes and unhide all rows:
Sub Unhide_checkboxes()
Dim CB As Shape
Dim sh As Worksheet
Set sh = ActiveSheet
For Each CB In sh.Shapes
If CB.Type =
msoFormControl Then
If
CB.FormControlType = xlCheckBox Then
'MsgBox CB.Name,
vbOKOnly
'if check box is
checked, keep it visible, otherwise, hide it
If
CB.OLEFormat.Object.Value = 1 Then
CB.OLEFormat.Object.Visible = True
Else
CB.OLEFormat.Object.Visible = True
End If
End If
End If
Next CB
'now hide the rows
BeginRow = 1
EndRow = 5
ChkCol = 2
For RowCnt =
BeginRow To EndRow
If
Cells(RowCnt, ChkCol).Value = False Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub
How to insert a
button and link to a macro
Finally, for ease of use we can add two buttons to our sheet
to run each one of the macros. Go to Developer tab, insert command button.
Watch the video below to see how the macro to hide
checkboxes and rows works.
As you can see, there is much to glean just from this one
example.