Alright, let me provide more background. I was trying to avoid boring the community with my whole shpeal, but... My company designs and manufactures gages our customers use to verify the tubes they make were made correctly. Ford makes cars. Cars have tubes. Ford designs the tube, but hires another company to make them. That company makes millions of tubes, that must all be as identical as possible. The gage is something their operator can use to verify the tube is correct before they ship them to Ford. Each of our gages has some or many components, called "features" that will check certain dimensions of the tube. For example, we would make an object to engage the part at the end of the tube to make sure the diameter is correct. We might also make another one for the operator to verify the mounting bracket is in the right spot, etc.
Believe me when I say these features come in hundreds and hundreds of varieties and we rarely make the same gage twice. Since everything we make is unique, it is difficult to determine quote price or lead time without an accurate way to measure the material and labor costs. I've been tasked to quantify our feature quotation process.
Your Apple Pie analogy was actually pretty good. In a way, each of our features is a combination of subcomponents. They can be categorized as far as the type of feature they are checking, the material used, what kind of motion they might require, locking method, mounting options, etc. Over 150 and it is likely to grow.
I have a big list of these options in a column. Each option's row has a material cost, amount of time required to make it, and a true/false value. When this or other options are selected (TRUE), then a subtotal will add up the material cost and time to spit out a price and required labor in various departments for this custom-built feature, as well as a bill of materials for any vendor items that might be required to build it. Great...
Unfortunately, that spreadsheet gets very busy on the eyes and the idea is for the engineer to be able to look at a customer's print, determine the type of feature to quote, select the appropriate options and quote the job in a way that effectively accounts for our costs.
Instead of seeing a whole table of information, I wanted to give them only the options necessary to get them started: "Feature Type", and a handful of options. Type A, Type B, etc. The second category of options might have 20 items, but only some of them are compatible with Type A, so I don't want the others to distract the engineer. When they select Type A, then only the compatible options in Category 2 are available. Based on what they select there, category 3 options that are compatible show up... and so on until they have make all relevant selections and have their price and timing.
....
- I have the options in column B.
- Column C is the setting that will trigger the Subtotals. This column is mostly true or false, but some options require a quanity. (that might be tricky, but we can get into that later).
- Columns D:L have to do with material costs and time. Those are static values and don't need any special coding, because I have formulas to consider them, based on conditions in column C.
- Column M is for when an item is required. For example, when somebody selects locking style: Option 2, it requires an inventory item, which needs to feed into a bill of materials.
- Column N will populate the item required for that row, if Column C is true. So, when all options are selected, I can get a list from N for all necessary inventory items to make this feature.
- Column O... we'll come back to column O
- Column P will list relevant options for column B, based on the option(s) selected in the group of rows above it (category A = rows 8:22). This column used a formula to list the compatible options without blanks.
More on Column P: This will populate based on a compatibility grid I created to the right of this table.
- All Category Names and Options listed in Column B8:B55 are transposed to row 8, beginning at column R. (I've been scouring and compiling some pretty cool code for that, so when a row in inserted, a corresponding column is inserted and a sub runs to transpose the list again, so the names and column headers always match. B9.value = R8.value. B10.value = S8.value and so on.
- Cell B8 contains the name of Category A: "Feature Type". B9 is option 1 of Category A: "ID".
- B22 is the Name of Category 2: "Feature Styles" and B23 is option 1 of Category B: "Quick Connect".
- "Quick Connect" is compatible with "ID", so i will put "ID" into cell R23, as well as into any other cells in that column that are compatible with the ID feature Type.
- P23:P33 (Feature Styles Category Options) has array formulas that will look for "True" in the "Feature Type" section (C9:C21), then get it's name from the cell next to it in column B and look for that value in the rest of the row in columns R:BL.
- Feature Type: "ID" is selected by marking C9 as True, then P23.value = "Quick Connect" because the text "ID" was found in R23:BL23.
- I'd like a group of checkboxes with captions for all Feature Types B9:B21.
- Column P will essentially be the captions for the visible group of checkboxes that are compatible with the Feature Type Option selected. There could be only 2 checkboxes, or there could be 7.
- Of those checkboxes available to the user, I want the checkbox to link to the cell next to the one feeding it's caption. Checkbox1's caption would be P23 and when clicked, would populate TRUE into Q23.
This brings us back to column O. Let's say the only Feature Style compatible with ID was option 4 in B26: "Oval". When ID is selected, P23 would say: "oval". That row is otherwise dedicated to the first Feature Style option: "Quick Connect" from B23. Since only "Oval" is compatible with ID in this example, there should be only 1 checkbox for the user to select. it's caption would be there in P23- "oval" and when clicked, Q23 would say True, but the formula in O23:O33 will see which checkbox was clicked by looking for TRUE in Q23:Q33, then return the name of the selected option in the corresponding row. In this case; O26 = "Oval".
If O26 says "Oval", then that means the user has selected this option, so I need that to trigger C26 to say TRUE, so the material costs and time can subtotal correctly. A formula in column C would be easy, but I can't have a formula in that column. Long story. Just can't do it. I has to happen with a click event triggering a code. The problem, is that I'd have to program EVERY checkbox to run that code. That's why i wanted a way for the code to trigger when ANY checkbox is clicked. (By the way, that code you referenced from the beginning has evolved quite a bit since then, but the principle is still the same. if O matches B, then C=True.
Whew... i've worn a hole in my keyboard.
I've added quite a bit of code since this thing started and if you'd like me to post the latest code and see if you can simplify this for me, then I'm all ears. I'm doing ok with this, except for a major setback in the class module that effectively runs the sub with ANY checkbox click (as I was hoping), somehow breaks when I group the checkboxes together for visual positioning. I want to still be able to drag them around in groups and modify their properties individually.
I want Checkboxes for each Feature Type Option and each Feature Style Option. Feature Style Checkboxes should only become visible when a Feature Type Option is selected. Only the relevant ones will be visible. Their captions will come from column P and they will be linked to Column Q. When clicked, they trigger the sub.
My goodness. my fingers are gonna fall off. If you've read this far down, you deserve a cookie.
