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.

 

[My Aswer Is This]

Yes. Show us your script that works.

 

[figment222]

Yes. Show us your script that works.

Ok, so a guy named jsotola helped me with this code. he had me create a class module named ChkClass with the following code:

Option Explicit


Public WithEvents ChkBoxGroup As MSForms.CheckBox


Private Sub ChkBoxGroup_Change()
    Debug.Print "ChkBoxGroup_Change"
End Sub


Private Sub ChkBoxGroup_Click()
    Debug.Print "ChkBoxGroup_Click"; vbTab;
    Debug.Print ChkBoxGroup.Caption; vbTab; ChkBoxGroup.Value
    ChkBoxGroup.TopLeftCell.Offset(0, 2) = ChkBoxGroup.Value
    Dim i As Long




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


End Sub

(I don't know what the debug.print stuff and the vbtab means, but I added the loop code --- Range("B" & i).Value = Range("c" & i) ---, which I saw somewhere else)

Then he had me put the following code into Module1:

Option Explicit


Dim CheckBoxes() As New ChkClass
Const numChkBoxes = 20
'


Sub doCheckBoxes()
    makeCheckBoxes
    activateCheckBoxes
End Sub


Sub makeCheckBoxes()       ' creates a column of checkBoxes


    Dim sht As Worksheet
    Set sht = ActiveSheet


    Dim i As Integer
    For i = 1 To sht.Shapes.Count
    '    Debug.Print sht.Shapes(1).Properties
        sht.Shapes(1).Delete
        DoEvents
    Next i


    Dim xSize As Integer:    xSize = 2      ' horizontal size (number of cells)
    Dim ySize As Integer:    ySize = 1      ' vertical size


    Dim t As Range
    Set t = sht.Range("g2").Resize(ySize, xSize)


    For i = 1 To numChkBoxes
        sht.Shapes.AddOLEObject ClassType:="Forms.CheckBox.1", Left:=t.Left, Top:=t.Top, Width:=t.Width - 2, Height:=t.Height
        DoEvents
        Set t = t.Offset(ySize)
    Next i


End Sub


Sub activateCheckBoxes()       ' assigns all checkBoxes on worksheet to ChkClass.ChkBoxGroup


    Dim sht As Worksheet
    Set sht = ActiveSheet


    ReDim CheckBoxes(1 To 1)


    Dim i As Integer
    For i = 1 To sht.Shapes.Count


        ReDim Preserve CheckBoxes(1 To i)
        Set CheckBoxes(i).ChkBoxGroup = sht.Shapes(i).OLEFormat.Object.Object


    Next i


End Sub

This added some subs to run manually (F5) to create checkboxes and then another to "activate them" or assign all checkboxes on the sheet to the Checkbox group the Class Module set up.

Then there is some tiny script in the sheet code to run this sub on sheet activation.

Private Sub Worksheet_Activate()
    activateCheckBoxes
End Sub

I've tested it out and can confirm that I can put a random value into C5: "Cake" and when I click on ANY checkbox, B5 will populate with: "Cake". Brilliant! Now to adapt this code to my 'real' workbook without breaking everything!

 

[MickG]

Doing what you want :- "Run Macro From any Checkbox" , would be a lot easier with "Forms" CheckBoxes.
And without a Class module.

 

[My Aswer Is This]

I would like to see the script for doing it your way. I did not know any way unless you put the same script in all check boxes. Maybe I can use your script next time the issue arrives. Does seem like a hard way to do things bulding a class.

Doing what you want :- "Run Macro From any Checkbox" , would be a lot easier with "Forms" CheckBoxes.
And without a Class module.

 

[MickG]

With the sub name required to run called "ChBox", run the code below:-

Dim cb As Object
For Each cb In ActiveSheet.CheckBoxes
    cb.OnAction = "ChBox"
Next cb

 

[My Aswer Is This]

Thanks.

With the sub name required to run called "ChBox", run the code below:-

Dim cb As Object
For Each cb In ActiveSheet.CheckBoxes
    cb.OnAction = "ChBox"
Next cb

 

[figment222]

With the sub name required to run called "ChBox", run the code below:-

Dim cb As Object
For Each cb In ActiveSheet.CheckBoxes
    cb.OnAction = "ChBox"
Next cb

So this would allow me to call a sub called "ChBox", when any "Form" (Not ActiveX) checkbox is clicked?

At this point, I've modified the code for my sheet and it is working well, but I am having trouble with it when I try to group the checkboxes together so that i can move them around to create the user form. I believe the issue is because I am already grouping them together in the class module, so selecting two of them on the sheet, right clicking and grouping them is destroying the functionality of the code.

Would I have that same issue with Form checkboxes?

 

[MickG]

The purpose of my code was to do away with the need for a class module.
Its not possible to use Forms controls within a "Userform", so perhaps you do need the Class module.
I think you need to clarify exactly what you are trying to do in order to help you further.

 

[figment222]

The purpose of my code was to do away with the need for a class module.
Its not possible to use Forms controls within a "Userform", so perhaps you do need the Class module.
I think you need to clarify exactly what you are trying to do in order to help you further.

Thank you for the reply. I'm new to this and learning as I go. When I say "user form", I think it is different than what the experts might call a "user form". For me, I have a spreadsheet with a bunch of options and formulas for a user to build a custom product. I have formulas that and conditional formatting, but it easily gets confusing for a user to see all this mumbo jumbo all over the place, not to mention the cumbersome and fragile nature of having people manually input values or saying "true" or "false" line after line. I thought I would have checkboxes control those values and use code to limit their visibility to only the ones relevant and compatible to previously selected options.

the activeX controls seem to have more flexibility, so I went with those, but I needed the class moduel because I wanted code to run anytime one of them was clicked. Unfortunately, I'm stuck with them all existing individually unless I can come up with a way to group them together, without breaking the class module functionality.

Let me summarize it like this: I want to have 150 checkboxes to run the code in the class module when they are clicked, but I also want to be able to group small selections of them, such as: All 10 Checkboxes for Option Category B will only be visible when one of them from category B is selected. And so on. I at least want to be able to group them together in a way that I can grab chunks of them to arrange visually.

Sorry, I know I don't have the terminology down to even ask the right questions. I could post a sample file, if you think that would help.

 

[My Aswer Is This]

I have been following this thread from the beginning.
In your original post you wanted this to happen when you click any checkbox:

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

But you never explained what your attempting to do and in your last post you only said:"for a user to build a custom product."


Since you say your new to all this I would think it would be more helpful to explain in more detail what your attempting to do. And maybe we have a easier way to do things.

Now I normally quit making comments or suggestions when more then one person is providing help to a question. I'm just trying to give the poster a answer. And I not trying to compete as to who's answer is the most properly scripted or the fastest way.

But I would be willing to help if you could go back and explain what you wanting and not tell us how you want it done.

And provide details like this:

When this cell value changes do this.

And we need details like when a value in column A changes make this change in adjacent cell in column "B"
And why must it be done when a checkbox is clicked. Having 150 checkboxes on a sheet and trying to hide some checkboxes and then unhide others and then running same code on some but not others seems like a hard way to do things.

And if the final result is to as you said: "for a user to build a custom product."
That seems like a very simple thing to do if we just knew the details of how that's done and not told how you want to do it.

For example if I want to make a Apple Pie I need:
Flour, Apples, Salt, Sugar.

So I put "Apple Pie" in cell "A2" and automatically we could have Flour show up in column "B" and "Apples" show up in column "C" and "Salt" in column "D" and "Sugar" in column "E" in the adjacent cells next to "Apple Pie"

But we would need to know more about what exactly you want to do other than "for a user to build a custom product"