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.
- Right click one checkbox in the worksheet.
- And then click the option “Format Control” in the sub menu.
- 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.
- 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”.
- 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.
- Click a cell where you need to show the contents. Here we click the cell C2 in this worksheet.
- 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.
- And then press the button “Enter” on the keyboard. Therefore, you will see the result immediately in the cell.
- 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.
- Press the shortcut keys “Alt +F11” on the keyboard.
- Next click the button “Insert” in the toolbar.
- And then choose the option “Module” in the drop-down menu.
- 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.
- And now click in the second sub.
- After that, click the button “Run Sub” or press the button “F5” on the keyboard to run the second sub.
- 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.
|
Comparison |
Link Cells | Use IF Function |
Use VBA Macros |
|
Advantages |
1. This method is very easy to use if you only need to show “TRUE” or “FALSE” in cells.
2. Compared with the other two methods, this is the most convenient method. |
1. You can input special contents into the formula according to your need.
2. If you are not familiar with VBA macros, you can use this function to show contents. |
1. All the checkboxes are assigned with macros. And you can see the contents when you check or uncheck the checkboxes.
2. You can show certain contents in cells according to your need. |
|
Disadvantages |
1. Link cells one by one can cost you a lot of time and energy.
2. Cells can only display “TRUE” or “FALSE”, which can be inconvenient in certain situation. |
1. In using this function, you also need to link cells to checkboxes one by one.
2. Showing “TRUE” or “FALSE” together with your designated contents at the same time will mess up your worksheet. |
1. If you are not familiar with Excel VBA, you probably meet with problems when you run the macro.
2. Using VBA codes will make the task more complex. |
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
Leave a Reply