How to Highlight a Cell or Row with a Checkbox in Excel | SKYXCEL 

Thanks! We are always happy to help!

Any answer to this?

i hope someone could help.. same question

No

Have you figured it out? Im in the same situation

Hello! Great question, the only way this is possible is to use VBA. After you get all of your checkboxes in place try to follow these steps and hopefully it helps! 1. Select the Developer tab in the Ribbon. If you do not see the Developer tab you will need to activate it by selecting File, Options, Customize Ribbon and then check the box labeled "Developer". 2. Select Visual Basic. 3. Click Insert, Module. 4. Within the new Module code window, copy and paste the code below Sub LinkChkBoxes() Dim chk As CheckBox Dim lCol As Long 'number of columns to the right for link 'if column is to the left put a negative number lCol = -1 For Each chk In ActiveSheet.CheckBoxes With chk .LinkedCell = .TopLeftCell.Offset(1, lCol).Address End With Next chk End Sub 5. Make sure the sheet with the checkboxes is activated. 6. Adjust the lCol variable by how many columns to the right or left of the checkbox is the cell you want the checkbox linked to if necessary. 7. Run the code by clicking the green play button or by pressing F5 on your keyboard. Please let me know if you have any further questions!

@@skyxcel6080 thank you so much!

Hi Anand, thank you for the feedback. To answer your question in short, yes it is possible to achieve that capability, but only through Excel VBA aka Macros.

@@skyxcel6080 Ok , is that some thing very difficult to do ? or is that a set script that can be used for this purpose?

@@mehappym The script will have to be customized for your particular situation because the script will need to loop through the checkboxes in order with a proper naming convention of "Chkbox1", "Chkbox2" and so on to see if the one above is set to "TRUE" or "FALSE" then set the locked/enabled property of the checkbox based on the previous checkbox value. Unfortunately, there is no "one size fits all" script for this purpose. Feel free to submit a contact forum through my website www.skyxcel.com if you would like me to complete this task for you or I am always happy to help guide you as you make progress.

Hello, great question. Yes, you would have to do it manually which would not be ideal. Maybe using special characters to activate the conditional formatting rules rather than checkboxes would be a better solution. However, if you wish to have a ton of checkboxes then you can automatically link the checkbox to its adjacent cell by applying VBA code.

Can you show me how to do this please ?

More than likely, but it might depend on how your spreadsheet is setup and how you are adding depending task. Some VBA may be required. I would have to see an example to come up with a more direct answer. If you would like to share I would be more than happy to see if I can come up with a solution for you!

@@skyxcel6080 Where would I need to send the information to?

Hello, it depends on what do you mean by using two variables. Is the situation where you want to bring in info using lookup value "xyz" and if nothing is found then use "123" as the lookup value or is the situation where you need to find data that matches both lookup values "xyz" AND "123". For the first situation you would want to use this formula: =IFERROR(VLOOKUP("xyz",$A$1:$C$50,2,0),IFERROR(VLOOKUP("123",$A$1:$C$50,2,0),"Not Found")) For the second situation you would want to use this formula: =VLOOKUP("xyz"&"123",CHOOSE({1,2},$A$1:$A$50&$B$1:$B$50,$C$1:$C$50),2,0) $A$1:$A$50 being the first column to lookup for "xyz" $B$1:$B$50 being the second column to lookup for "123" $C$1:$C$50 being the data column that you would like to return Please let me know if you have any further questions, thanks!

@@skyxcel6080 second example is my case and it helped a lot. Thanksss.

Hello Aixa, Great question. Unfortunately, the size of the check box itself cannot be changed for Form Control check boxes. However, there is a work around to change the size of the checkbox if you use an ActiveX Control checkbox. When you go to the Developer tab and click Insert you will see a section called ActiveX Controls. Click the checkbox icon and place it in a cell. Make sure you are in Design Mode, you can turn Design Mode on and off by clicking Design Mode under the Developer tab. Then right click the check box and select Format Control. Within the Properties tab select the Move and size with cells option. Now if you adjust the row height and column width you will see the checkbox change in size, unfortunately if you have text associated with the checkbox the text will change in size as well. Keep in mind ActiveX Controls work differently than Form Controls and they are not available on Mac. I will not go into too much detail about their differences, but to link a cell to an ActiveX checkbox right click the checkbox and select Properties. Then you can type in the cell address that you want linked into the LinkedCell field. Once you have completed that make sure to turn off Design Mode and you will see the linked cell change from TRUE to FALSE as you check and uncheck the checkbox. I hope this helps and please don't hesitate to ask any questions you may have. Thanks!

@@skyxcel6080 thanks so mucho, it is very useful

Hi Kaze, unfortunately the only way to automatically change the cell link reference is to use Excel VBA to write a custom macro. I will try my best to walk you through step by step on how to implement and run this macro. Step 1: Make sure the row height allows enough room for the checkbox. WARNING: if there is not enough room all your linked cells will be one row above the checkbox row. Step 2: Click the Developer tab and select Visual Basic. This will open the Visual Basic Editor (VBE). Step 3: In the VBE click the Insert tab and select Module. Step 4: Copy and Paste the code below into the code window. Pay ATTENTION to my comment within the code with the given example about how many columns to the right or left of the checkbox you want your linked cell to be. Sub LinkCheckBoxes() Dim chk As CheckBox Dim lCol As Long lCol = -1 'number of columns to the left (negative) or right (positive) of check box 'EX: if you want the linked cell to be 2 columns to the right you would put 2 instead of -1. 'loop through each check box on the active sheet For Each chk In ActiveSheet.CheckBoxes With chk .LinkedCell = .TopLeftCell.Offset(0, lCol).Address End With Next chk End Sub Step 5: In Excel make sure you have the sheet you want to perform this macro on is the active sheet. Step 6: Save your file before running the macro because there is no undo after you run a macro. Step 7: Run the macro. There is a few way to run a macro, but one method is to click the Developer tab in Excel then select Macros. You should see our macro named LinkCheckBoxes, select it and then click Run. Give this a try and let me know if you have any further questions. Thank you!

@@skyxcel6080 OMG thank you, I was not even expecting to get reply 😂 Thank you sir! 🙇

Hello, it is great to hear that you could apply this to other real life situations. To apply this and be able to filter data it will require using ActiveX Controls rather than Form Controls and make sure the CheckBox properties are set to "Move and size with cells". This way when you hide rows using a filter the CheckBox will hide as well. Another method would require the use of VBA. When linking a cell with ActiveX Controls you have to right-click the CheckBox, select "Properties" and in the "LinkedCell" field type in the cell reference. See how far that gets you and let me know if you have any further questions. Thanks!

Hello, Great question! Give this a try and let me know if it works for you. 1. Right click the checkbox 2. Click Format Control... 3. Click the Protection tab 4. Unselect the box labeled "Locked" 5. Click Ok 6. Select the linked cells that your checkboxes use that input TRUE or FALSE. In the video this range would be B4:B6. 7. Right click the selected range and click Format Cells... 8. Click the Protection tab 9. Uncheck the box labeled "Locked" 10. Click OK 11. Protect the sheet and test it out!

Hi Richard, great question. To allow check boxes to function correctly we have to change the Protection settings of the actual cells that are linked to the check boxes. I will be referencing the cells used in the video B4:B6. Please follow the steps below: 1. Click the Review tab and make sure the sheet is unprotected. 2. Select cells B4:B6. 3. Right click your selection and select Format Cells... 4. Select the Protection tab and click the Locked check box until the check box is blank. 5. Click OK. 6. Click the Review tab and select Protect Sheet. Now you should be able to use the check boxes on a protected sheet without getting any error messages. Let me know if you have any more questions. Thanks!