Change Chart Axis Font Color with a Checkbox

[JasonBoyd]

Hi all,

Been searching the forums and the internet for a way to change the font color of a chart axis when a checkbox is set to true. I haven't been successful yet so that is why I am posting.

let checkbox be checkbox5 and chart be chart5. What I would like to happen is that if checkbox5 is true then the font color of the horizontal axis changed to grey (RGB 166,166,166), if it is false then I would like the font color of the horizontal axis to be black (RGB 0,0,0). I also would like this macro to be run every time the checkbox changes, or the corresponding cell changes to TRUE or FALSE (it is in another sheet, say sheet2). I am not sure what I am missing but it might be something silly. This is the code I have so far but all it is doing is showing the chart as selected and not changing the font color. I would also like it to not physically select the chart, this is for a dashboard so the selecting of the chart throws off some of the visuals a bit, but not sure how to do change the chart values without selecting it.

Please Login or Register  to view this content.

[Sbarro79]

Try something like this:

Please Login or Register  to view this content.

[JasonBoyd]

Thanks for the reply.

That code looks a lot cleaner than mine was, but doesn't seem to be doing the trick either. I have put the code in the sheet containing the checkbox and chart but clicking the checkbox on and off isn't having the desired result. If I select the first line of the macro and click run I get this error: Run-time error '438': Object doesn't support this property or method.

Is there any way to double check the check box label? If I right click it on the worksheet it is labeled as "Check Box 5"..does that mean it is also CheckBox5?

[Sbarro79]

Hi Jason

Sorry my bad, I didn't realize that you used a Form formant not Activex formant.

So, now try this:

Right click on your checkbox, choose "assign macro", then if you have something like Checkbox5_Click on the list select it, choose Edit and then paste this code:

Please Login or Register  to view this content.

Hope this help you.

[JasonBoyd]

Would it be more efficient to switch the check boxes to ActiveX?

[Sbarro79]

I'm sorry, but I'm not experienced enough in VBA to tell for 100% which one would be more efficient.
But I think because of both codes are very simple, so difference in execution time and pc resources usage should be barely unnoticeable.
As far as I know (and this comes from person very experienced in VBA) if you use controls for simple applications then use Form controls, otherwise use ActiveX.

[Andy Pope]

for form checkbox

Please Login or Register  to view this content.

[JasonBoyd]

Hi Jason

Sorry my bad, I didn't realize that you used a Form formant not Activex formant.

So, now try this:

Right click on your checkbox, choose "assign macro", then if you have something like Checkbox5_Click on the list select it, choose Edit and then paste this code:

Please Login or Register  to view this content.

Hope this help you.

Thanks, I got that to work but have now run into a new problem. I also have an "All" checkbox, that when clicked either makes all other checkboxes true or false. If I use that then the font color on the chart doesn't change. I could add a similar macro to that checkbox but then it is only changing the font color based on what it is and not on whether or not the checkbox beside it is true or false. I think I might try switching the checkboxes to ActiveX and trying your other suggested code and see if that solves it.


EDIT: got the ActiveX checkboxes inserted but ran into a couple of aesthetics issues that I can't figure out. First one, if a checkbox does not have a caption it looks kind of blurred..can that be changed? Second, when there is a caption when the checkbox is clicked the caption looks like it is selected (dashed box goes around the caption), how do I make it so that does not happen?