Form Controls - Checkboxes

[smarie123]

I’ve created a form for users to fill out in excel using a mixture of free text fields and several checkboxes. My checkboxes are not linked to cells because it’s not a form that uses data or that needs “true” and “false” values. Is there ANY way to be able to change the color of the check mark in the box (or even the background color of the checkbox) if the box is checked? I know formatting the box can change the color of the entire thing, but it’s not conditional it just changes the color in general.
So for example: if a box is checked – the check mark turns red. That way it’s easy for the manager to pick out which things were done or which supplies are needed
I’m not too familiar with macros but if someone tells me that’s the only way then just tell me what the macro would be and where I would put it. This would be MOST MOST MOST appreciated!!!!!!

 

[Jerry Sullivan]

Two options to consider...

1. In lieu of using ActiveX or Form Checkboxes try using "Marlett Checkboxes" approach. That approach uses VBA to toggle font symbol characters to display either checkbox checked or checkbox unchecked.
This article on Ozgrid explains how to set this up with some example code.
Excel VBA: Tick Cell Upon Selection

Once you have this setup to toggle checked and unchecked, you could use conditional formatting to change the color of the font based on the value of the cell (or you can use the same VBA procedure that toggles the checkbox to toggle the color).

2. Link your form controls to cells in a hidden column. Then add conditional formatting to change the color of the font based on the value of the cell in the hidden column on the same row as the checkbox.

 

[smarie123]

Thanks, Jerry. A few questions...
1.) The hidden cell idea is the one I'm tying out, but I can't do conditional formatting inside of the content box. Any way around that?

 

[smarie123]

There's an option inside of the checkbox formatting for a macro...I'm not extremely familiar with macros, but can something go in there?

 

[Jerry Sullivan]

Not sure what I was thinking when I suggested that you could change the color of a form control checkbox using a helper cell- I think I muddled together the approach of the Marlett Checkboxes with the True-False properties of a Checkbox.

As far as I know the color of the form control checkmark can't be changed. If you use ActiveX checkboxes, you could use VBA to change the color of the Text, but not the checkbox/checkmark.

You can change the background color of the checkbox form control (as you noted in your OP). tlowry provides a good example of how to set this up in this thread.
http://www.mrexcel.com/forum/excel-...l-checkbox-background-color-when-checked.html

If you want the color of the checkmarks to change, then Marlett checkboxes seems like the best approach.

I understand you are new to using VBA. If either of those options appeals to you, I'd be glad to help with any questions you have about how to implement the code examples in your workbook.

 

[smarie123]

Thanks, Jerry! I ended up rearranging the entire document to be able to link checkboxes to hidden cells in order the change the color of the text when checked. That is working well so far! Thanks for all your help!