Trigger Sub when ANY checkbox is clicked

[figment222]

Hello, I have a macro that will duplicate the value cells C3:C10 to the cell in column B directly next to it. So, if C4=Bacon, then it will automatically put "Bacon" into B4. Awesome... now, I need to find a way to have this be triggered by ANY checkbox on the sheet being checked. I can link the checkbox to C4, but B4 won't change until I select something else. I need it to change when I click the checkbox.

If anyone sees this and recommends the obvious answer to use the CheckBox1_Click() method, then please know that I need to trigger the sub when ANY checkbox is clicked. Not having much luck finding anything, though...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim i As Long


    For i = 3 To 11
        Range("B" & i).Value = Range("c" & i)
    Next i

I've seen a few articles talking about making a class module, but I can't get it to work. I'm new to VBA, so please explain it like you would to an 8-yr old.

 

[figment222]

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.

 

[My Aswer Is This]

Wow. That is a complete explanation for sure. This would be way beyond my knowledgebase.
But there are a lot of people here on Mr. Excel who may want to join in here and help you.

 

[figment222]

hahaha. You asked for the full background, so there it is. Actually, I have a lot of the functionality covered so far, but I'm really stuck on being able to group the checkboxes together. I guess if it comes down to it, I can live without it, but that's gonna be a LOT of manually moving them around and such.

Maybe I can write a sub that will make the whole spreadsheet invisible with a command button, except for the controls. If I need to move groups of controls, maybe I can write a sub that will either move them or delete them and make more of them at a desired location. They'd have to be renamed exactly the same way to maintain other subs that might rely on them. So, checkbox5 would have to be deleted and created again as checkbox5, so that other subs referencing checkbox 5 will still work.

how's that sound?

 

[My Aswer Is This]

Not sure how much this helps but do you know you can group textboxes into a group.
In Developer mode:
Select all the textboxes or any other controls you want then right click and select group. Look in the Box above Range("A1") and you should see a little box with the group name.

Then you could use a script like this to toggle the visibility of the group.

ActiveSheet.Shapes("Group 2").Visible = Not Shapes("Group 2").Visible

 

[figment222]

Sorry for the delay, but I was chasing another rabbit. Since that rabbit has been caught, I'm coming back to the checkboxes issue. The code that enables clicking ANY checkbox to trigger a sub is in module1 and basically assigns all checkboxes on worksheet to the ChkClass.ChkBoxGroup assigned in the class module.

The class module also contains the macro code that runs when any of them are clicked. I put the name of that macro into the sheet module for selection change and it works great, but when I try to group the checkboxes in the way you've described, I can see that they are "group 2", but when I make a selection change, I get an error, pointing me back to module1 and highlighting this line:

Set CheckBoxes(i).ChkBoxGroup = sht.Shapes(i).OLEFormat.Object.Object

Because this thing works by assigning all checkboxes to a group, I guess it makes sense that it breaks when I assign any of them to a different group.

I wonder if it would be better for me to find a way to move the checkboxes with code... makes my brain melt just thinking about it.

I do have some code that will generate the checkboxes at a certain spot, but I also want to define certain parameters, such as linking the caption to a certain cell, etc. I wonder if I can modify it in a way that will NAME the checkboxes a certain way, then I don't have to group them together.

Actually... I wouldn't have to worry about ANY of this crap if there was just a better way to trigger my macro, other than all this class module stuff. I'd like to just make a checkbox, program it to look at a certain cell for it's caption, link it to the cell next to the caption and then be able to trigger my macro adjust other cells. grrrr

I've come up with a loop that will set the caption for all checkboxes, but I can only get it to set the same caption for all of them, rather than looping through a range for the cell.value. any ideas are appreciated.