Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to change a specified cell value or color when checkbox is checked in Excel?

This article is talking about changing the value or color of a specified cell by a checkbox. For example, when a certain checkbox is checked, the value or background color of a specified cell will be changed automatically. Please follow the below methods step by step to deal with it.

Change a specified cell value when checkbox is checked with formula

Change a specified cell value when checkbox is checked with VBA code

Change a specified cell color when checkbox is checked with VBA code

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial


arrow blue right bubble Change a specified cell value when checkbox is checked with formula


Supposing you need cell A1 to be populated with value “test” when the check box 1 is checked. And blank the cell when unchecking the check box. Please do as follows.

You need to link the checkbox to a certain cell firstly, and then apply formula to achieve it.

1. To link the checkbox to a certain cell, please select it, then enter= cell reference into the Formula Bar and press the Enter key. Here I link the checkbox 1 to cell C2 with entering =C2 into the Formula Bar after selecting the checkbox.

For ActiveX Control check box, please right click it and select Properties from the right-clicking menu. And in the Properties dialog box, enter the linked cell into the LinkedCell field, and then close the dialog box. See screenshot:

2. Select the cell you need to change its value (here I select A1), then enter formula =IF(C2,"Test","") into the Formula Bar, and press the Enter key. See screenshot:

Note: In the formula, C2 is the linked cell of the check box 1, “Test” is the value you need to display in cell A1 when the check box is checked.

You can see the results as below screenshots shown after checking or unchecking the corresponding check box.


arrow blue right bubble Change a specified cell value when checkbox is checked with VBA code

You can also change a specified cell value when checkbox is checked with VBA code. Please do as follows.

1. Right click the Sheet Tab with the cell value you need to change based on checkbox, then click View Code from the right-clicking menu.

2. In the Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the Code window.

VBA code: Change a specified cell value when checkbox is checked

Private Sub CheckBox1_Click()
Dim xRng As Range
Dim xCell As Range, xStr As String
Set xRng = Selection
If CheckBox1.Value = True Then
    xStr = InputBox("Please enter a value:", "Kutools for Excel")
    xRng.Value = xStr
    If xStr = "" Then CheckBox1.Value = False
Else
    xRng.Value = ""
End If
End Sub

Note: In the code, ChekBox1 is the name of the Check Box (ActiveX Controls). You can change it as you need.

3. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.

4. Select a blank cell (A1) you want to change its value based on the checkbox, and check the checkbox1 in your worksheet. Now a Kutools for Excel dialog box pops up, please enter the value you want to be populated in the selected blank cell, and then click the OK button. See screenshot:

Then the specified value is displayed into the selected cell immediately. And when unchecking the checkbox, the cell will be blank.

Note: This code will remind you enter the text value circularly when you check the check box each time.


arrow blue right bubble Change a specified cell color when checkbox is checked with VBA code

This section will show you how to change a specified cell color when checkbox is checked with VBA code.

1. Right click the Sheet Tab with the cell color you need to change based on checkbox, then click View Code from the right-clicking menu.

2. In the Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the Code window.

VBA code: Change a specified cell color when checkbox is checked

Sub CheckBox1_Click()
Dim xRng As Range
Set xRng = Selection
If CheckBox1.Value = True Then
    xRng.Interior.Color = vbRed
Else
    xRng.Interior.Color = xlNone
End If
End Sub

Note: In the code, ChekBox1 is the name of the Check Box (ActiveX Controls). You can change it as you need.

3. Select a cell you need to change its background color, then check the checkbox. You can see the fill color of the selected cell is changed to red. And the selected cell will be filled with no background color after unchecking the checkbox. See screenshots:

Tip: If you want to insert multiple check boxes into a selected range in bulk, you can try the Batch Insert Check Boxes utility pf Kutools for Excel. Or batch insert multiple Option Buttons with the Batch Insert Option Buttons utility. Besides, you can delete all check boxes at once with the Batch Delete Check Boxes utility as below screenshots shown. You can go to free download the software with no limitation in 60 days.


arrow blue right bubbleRelated articles:


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  • Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  • Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  • No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  • Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.