How to hide checkboxes and rows in Excel with VBA

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.