How to Highlight a Cell or Row with a Checkbox in Excel | SKYXCEL
Vložit
- čas přidán 5. 08. 2024
- Welcome to SKYXCEL Tips & Tricks! Today we will be showing you how to highlight a cell or row based on if a checkbox is clicked or not. Combing user form controls and conditional formatting is a great start for creating more interactive and dynamic Excel spreadsheets.
Please give this video a like if you found this tutorial helpful and subscribe to receive updates for future tutorial videos that will save you time and increase your efficiency.
We invite you to leave any questions or request in the comments below!
In need of more Excel assistance specifically for you? Visit
www.skyxcel.com/ where we can design fully customizable, automated spreadsheets that are tailored specifically for your needs.
You can also visit our blog at www.skyxcel.com/blog for more Excel tips & tricks.
Thank you very much for the explanation.
It was very clear, calmly spoken and excellent explanation.
THANKS! MAY YOU HAVE A BLESSED LIFE!
THANK YOU! I could not find anyone else with a tutorial to this.
Very useful video. Thanks a lot!
Thank you...this tutorials such a big help for starters
Excellent, Very Complete and Well done.
Very useful for Bank reconciliations. Thanks a lot…!
Thank you!!!!This was so helpful!!!
Awesome tutorial. ty!
Comprehensive tutorial!
Great tutorial
Thank you!!!!!
Thank you soooo much!!
Great Video! You are a life saver!
Thanks! We are always happy to help!
Thanks.... Professor!....
Interesting. Thanks 😊
Thanks 🙏
Thank you
Thankkk youu
🔥🔥
Great video! my check boxes are so small (Mac excel) can't hey be made bigger? thanks!
If have 250 checkboxes, is there a quick way to format the control or do I have to each one manually?
Any answer to this?
i hope someone could help.. same question
No
Have you figured it out? Im in the same situation
I love your voice hahaha
Thank you. Can I use orange as an option in addition to green and red?
❤
So, if i want to "Format Control", I have to do it to each cell individually in a list of 500+ entries?
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 ?
Great Video! Is there a way to connect all check boxes to their individual rows without doing it one by one?
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!
My Default check box is way to small for what I want. How can I resize the Check Box itself? I'm not talking about the selection bounding box around it. Can I change the default size or resize after I have put it in the form? Nothing I try seems to work to resize the check box. Definitely not very intuitive.
great video. wondering if its possible to keep the cell locked and only able to tick if the cell above is ticked. So a step by step process. 1st box gets ticked ,so seconds gets unlocked but third remains locked. When second is ticked 3rd box gets unlocked. And so on. Is that possible at all ?
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.
I have a worksheet with the following data,,, vendors name in Column A2 , Paymnt Amount in Column B2. Check-boxes in Column C2, Paid/Unpaid text in Column D2.
I would like to establish a link between check-boxes and the 'amount' column. Specifically, I want the checkbox in column C2 to be automatically checked when I enter an amount in cell B2. Additionally, when the checkbox is automatically marked as checked, I would like the text 'Paid' to appear in column D2. Is it possible to achieve this using a formula or VBA/Macro?
wow. I would never have gotten that on my own...
hi, i need to link to 1000 entries. As per your comments, you mentioned it can be done via VBA. could you please create a new video on it showing how it can be done in VBA?
thanks a lot for the video.
How can we link to a long list?
I am looking for a way to format some dependent tasks if the trigger is checked done. Is there a way to do that in excel?
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?
what if I want to give a (no fill), when the checkbox is unchecked
ı have a question. we can bring a information from other file with vlookup according to one variable. but, whatıf ı want to use two variable to bring this information? which formula should ı use?
thank you.
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.
How do you move the box to the exact centre of the cell?
How can you resize the checkbox like this, i tried everything but it wont work
How to change the TRUE to Complete and FALSE to In Progress?
Is it possible to increase the size of the check box??
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
Not working in windows 10 i add tick box from developer option and i tick it countif formula not working
What if I want when click to select specific text, I want this text displays on the top of the form. Example: If select the Category Fax. I want the Fax word displays on the top on the cell C3, if selects Category Letter, shows the word Letter on the Category Field: Letter on C3. Only one category can be selected.. The reason I want this is people selects from big category list in the middle of the form up to the bottom with 2 columns of categories on the form, and the users have to keep scrowling down up and down to see the selected category. So placing the category name on the top is going to save a lot clicks up and down. We receive 100 forms per day with category selected. So if automated, we would receive the forms displaying the category displayed on the top, without deleting the categories list in the form.
What if I have 350 rows and don't want to manually do those true/false format controls?
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! 🙇
is it possible to do exactly this when some of the cells are locked.
This solution works, nearly, perfect, but won't work at all when the sheet is protected because some of the cells are locked..
I need to keep said cells locked because of the formulas they contain.
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!
Sir, I saw your video & applied in work, and it worked also, So thank you very much. But when it is applied into a filtered data having checkbox & checkboxes are selected randomly, it isn't working. Would you mind share your Email id which enable me to send the file to you to help me in this regard, 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!
How to protect worksheet while check box is able to check and uncheck
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!
WEEEEEEEEEEEEEEEEEEEEEELLLLLLLLLLLLLLLLLLL Doneeeeeeeeeeeeeeeeeeee
I fucking hate excel lottery followed every single second of your wonderful video and nope won’t work
Thank you