Checkboxes change multiple cells per row to a string which checked and formula when unchec

**Vertical** · 01-16-2014, 01:52 AM

I have a worksheet in excel 2010 which has 3 colored columns. Each column cell has a formula in it which checks the date in column H and changes the text to various values based from the formula. (See Example Below)

Example of formula in the 3 columns (Each column has a slightly different version) you can see these in the sub code below.

Formula:

Please Login or Register  to view this content.

Each cell in column J has a checkbox in it. For the 1st checkbox I have written the code below which runs on the event CheckBox1 "Click":

Please Login or Register  to view this content.

My problem is that I have like 70 checkboxes on this sheet and I want them all to do the same thing as this first checkbox does. It makes no sense to paste this same code 70 times and change all of the ranges in it. How can I write the rest of the subs to call this sub but change the ranges based on the ID number of the remaining checkboxes? For example Checkbox2 is on the row below CheckBox1. So being that I know that the row difference will always be consistant mathmatically how can I pass the checkbox ID number from Sub2 to the Sub above and change the ranges?

Its hard to explain what I'm trying to do so please let me know if you need more information or specifics on it. If you have an idea of a better way to do this (for the future in case I add additional rows or columns, etc. please let me know too)

Here is a picture of the spreadsheet. excel_checks.jpg

**Vertical** · 01-16-2014, 06:13 PM

Any ideas at all id love to hear them! I'm also willing to answer any questions I can about what im trying to do in more detail... I'm just stuck at this point and really need a little bitty push please!

I thought perhaps I could put all of the code in a module and call the module from the click code for each checkbox and have the module to check the state of the checkbox that called it but Im not quite certain how to pass the parameters that I need in order to perform those checks (Nor am I currently aware of how to make the module control the checkbox on the form since when I attempted this in the code it basically acted like the CheckBox1 didn't exist...

Anyway, looking for any ideas!!!!

By the way, here is the full workbook. Tracking-Supplier_Lab_ScopesAndCerts.xlsm

See the 2nd tab, click the 1st checkbox to see what the correct way I want it to act is. I just want to make the rest of the checkboxes to do the same thing as that first one. Might be easier to see what im trying to do with the working workbook...