3 Methods to Use Checkbox to Show or Hide the Cell Contents in Excel

Sometimes you need to use checkboxes to assist your work. And show certain contents in certain cells according to the values of checkboxes can show information clearer. In this article, we will show you 3 effective methods to show or hide cell contents.

In this image below, we have input the checkboxes into the worksheet. And now you need to show certain contents in certain cells.

When you check one checkbox, the certain cell needs to hide or show certain contents. And below are 3 methods to achieve this task.

Method 1: Link Cells

In this method, you can link the checkboxes to certain cells.

1. Right click one checkbox in the worksheet.
2. And then click the option “Format Control” in the sub menu.
3. After that, input the designated cell reference into the text box of “Cell link”. Here you can also use your mouse and select the cell directly. But remember to use the absolute reference. And in this example, we use the cell B2 as the link cell.
4. And then click the button “OK” in the current window.

Next you will come back to the worksheet. At first, there is no content in the cell B2.

When you check the checkbox, the value “TRUE” will appear in cell B2. And then if you uncheck the checkbox, the cell will show “FALSE”.

5. Now repeat the above steps and link other checkboxes with cells. Therefore, when the values of checkboxes change, the contents in linked cells will also change. Besides, you can also clear the contents in cells manually.

Method 2: Use IF Function

In this method, you need to make sure that the checkboxes are linked to certain cells. Thus, you can use the IF function to show certain contents.

1. Click a cell where you need to show the contents. Here we click the cell C2 in this worksheet.
2. And then input the following formula into the worksheet:

=IF(B2,”Available”, “Not Available”)

Here you can see that this formula will use the result in the linked cell B2. Thus, you also need to link cells for those checkboxes. In this formula, you can also change the contents according to your need.

3. And then press the button “Enter” on the keyboard. Therefore, you will see the result immediately in the cell.
4. Now double click the fill handle of cell C2 and fill the formula in other cells.

When you change the value of checkboxes, the result in cells will also change accordingly.

Method 3: Use VBA Macro

On the other hand, if you don’t want to link cells and want to show certain contents in certain cells, you can use the VBA macro. And here we will show you the steps to finish this task.

1. Press the shortcut keys “Alt +F11” on the keyboard.
2. Next click the button “Insert” in the toolbar.
3. And then choose the option “Module” in the drop-down menu.
4. Now copy the following VBA codes into the new module.

Sub ShowContent()

    Dim CheBox As CheckBox, i As Integer

    i = 2

    For Each CheBox In ActiveSheet.CheckBoxes

        If CheBox.Value = -4146 Then

            Range("C" & i) = "Not Available"

        Else

            If CheBox.Value = 1 Then

                Range("C" & i) = "Available"

            End If

        End If

        i = i + 1

    Next

End Sub

'assign macro for all the checkboxes in this worksheet

Sub AssignMacro()

    Dim CheBox As CheckBox

    For Each CheBox In ActiveSheet.CheckBoxes

        CheBox.OnAction = "ShowContent"

    Next CheBox

End Sub

There are two procedures in the code. The first sub will show certain contents in certain cells according to the checkboxes values. And the second sub will assign the first macro to all checkboxes in batch in this worksheet. Besides, in your actual worksheet, you may also change some elements to make the VBA codes available.

5. And now click in the second sub.
6. After that, click the button “Run Sub” or press the button “F5” on the keyboard to run the second sub.
7. Now you can go back to the worksheet.

When you check or uncheck one checkbox, the contents in the certain cell will also change accordingly. And you don’t need to link cells for those checkboxes.

A Comparison between the 3 Methods

To help you choose from the 3 methods, we have listed all the possible advantages and disadvantages in the table below.

From the above analysis, now you have a clear understanding about those different methods. Thus, the next time if you need to show certain contents in cells according to checkboxes values, you can choose a method according to your actual need.

Create a Backup Plan for Your Excel Files

To avoid the bad result from data disaster, one of the most effective methods is take backups for your files. Thus, whenever you meet with Excel file corruption, you will not suffer from the result. And for all your files, you need to create an effective backup plan. But condition also may exist that even the backup files are also damaged. At this moment, you can use a third party tool to repair Excel file corruption. With this recovery tool and backup files at hand, you will not loss data in a data disaster any more.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair docx document damage and outlook repair software products. For more information visit www.datanumen.com