Changing checkbox background color - loop to check all tick boxes on sheet for status TRUE

Hi there

I need to change the background colour for checkboxes on my spreadsheet. I have managed to do this for a single checkbox as follows:

Please Login or Register  to view this content.

Now, I have well over 100 checkboxes on this sheet (eg sheet7) and was hoping not to have to copy this code that many times to cover all. Would appreciate some help with creating a loop to check for all tick boxes on the sheet and apply the colour change to the checkbox background when it comes across a box that is ticked.

Many thanks in advance...

Sorry - didn't work - stays white whether ticked or not...

@ Norie - Active X . The code I posted worked for a single tick box, but would have to repeat it 100+ times for the other ones. Confidential document, so can't actually post the workbook as it also draws data from other spreadsheets.

Tick boxes however are just true/false status and as I said - my code works, but need someway to loop through all checkboxes and apply the same. Unsure how though.. :-(

It will reduce code just slightly - still have to call it 100+ times in that case, which I am trying to avoid in case the color needs to be changed in future. Hoping to only have to do this in one spot. Not that familiar with VBA, so am not sure how to call objects and use their attributes.

The error with Alan's code that comes up is RUn-time error '438': object doesn't support this property or method. at If objx.Value = True then objX.Backcolor = RGB(255,0,0)

@ Norrie

Pretty close to what I got to work in the end - slightly different, but still works.

Code as follows

Please Login or Register  to view this content.

Had to put the ElseIf in as otherwise my buttons were also changed to white background!

At the moment, I am calling this sub as part of a button press, so colour doesn't take effect until the button is pressed.
But wondering if there is any way to have the colour change as soon as the box is ticked / unticked (like a click event, but one which doesn't require all tick boxes to have this click event sub - one that covers all tick boxes if and when they are clicked).

Not sure if this can be done, but just putting it out there!

Thanks for the suggestions so far!

It hasn't entirely resolved my query yet. The original code I posted was a click event, which changed the colour immediately when the box was ticked.
The code I ended up with does loop through all the tick boxes, but will only apply the colouring AFTER a button is pressed to check all the tick boxes.

Was hoping to be able to modify this code further so that it takes effect the minute each tick box is selected, without relying on a button press after the whole survey has been completed.

Thank you...