Event triggered by ANY checkbox click

Question

I'm going crazy trying to find a way for code to run when I click on ANY of the checkboxes on my sheet. I've seen multiple articles talking about making a class module, but I can't seem to get it to work.

I have code that will populate column B to match column C. Whatever I manually type into C10 will populate into B10, even if C10 is a formula: =D9. So, I can type TRUE into D10 and the formula in C10 will result in: TRUE and then the code populates B10 to say: TRUE. Awesome... the trick is to have a checkbox linked to D10. When I click the checkbox, D10 says TRUE and the formula in C10 says TRUE, but that is as far as it goes. The VBA code does not recognize the checkbox click. If I then click on the sheet (selection change), then the code will run, so I know I need a different event.

It is easy enough to change the event to "Checkbox1_Click()", but I want it to work for ANY checkbox I click. I'm not having ANY luck after days of searching and trying different things.

here is the code I'm running so far

    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
    End Sub

Any help would be appreciated.

here is some code that i posted for handling multiple buttons .... see if you can adapt it to fit your needs ... https://stackoverflow.com/questions/46381935/change-activex-command-button-color-back-to-previous-color-after-clicked/46396731#46396731 — jsotola, Sep 30 '17 at 17:02
btw: you said that you can't get the class module code to run, but you did not post any code that refers to class modules. — jsotola, Sep 30 '17 at 17:03
why are you using a loop to assign values? .... just use `Range("B3:B11") = Range("c3:c11").Value` — jsotola, Sep 30 '17 at 17:10
i should have asked at the begining .... what type of checkboxes do you have? `active-x control` or `form control` ..... and, do you have a choice of which type you are going to use? — jsotola, Sep 30 '17 at 17:51
I went to the link you posted and created a new workbook where I pasted in the code you mentioned into the sheet module, created a class module, and then created a regular module (Module1), posting in the code, exactly as you had it. I can't get it to do anything. I tried to manually run the macro, but it pops up a message box saying: User-defined type note defined. It then highlights the following section of the code in the BtnClass module: WithEvents ButtonGroup As MSForms.CommandButton — csmith222, Sep 30 '17 at 17:57
I am using activeX checkboxes. To your other comment... I've tried multiple solutions that include class modules, but none of them work. — csmith222, Sep 30 '17 at 17:58
I am happy with the code I have to do what is necessary to the values, I just need an event that will happen when any checkbox is clicked. I'm going to have more than 100 checkboxes and don't want to write code for each click event. — csmith222, Sep 30 '17 at 17:59
the code works on command buttons, so you have to change the references to checkboxes .... are you able to do that? — jsotola, Sep 30 '17 at 18:06
I figured I would see how it worked the way it was, but I put the code into a blank sheet. I looked as if it would create the buttons. Sorry, I'm really new at this. Am I supposed to create a command button for this code to work? — csmith222, Sep 30 '17 at 19:38
yes, it should create buttons. just click anywhere in `makeButtons` sub and press `F5`. then click in `activateButtons` and press `F5` .... then just click on the buttons in the worksheet — jsotola, Oct 01 '17 at 00:45
I got it to work with F5! thank you! Having trouble converting to checkboxes, though. I modified the BtnClass code to "MSForms.CheckBox", rather than CommandButton, but when I run the activate sub, I get an error message box that says "Type mismatch". and it highlights this code in module1: Set Buttons(i).ButtonGroup = sht.Shapes(i).OLEFormat.Object.Object how do I adjust this? it seems to not be specific to neither commandbuttons, nor checkboxes. does it matter that they are activeX controls? — csmith222, Oct 01 '17 at 20:51

jsotola · Accepted Answer · 2017-10-02T02:47:22.310

this works

' this goes into sheet code

Private Sub Worksheet_Activate()
    activateCheckBoxes
End Sub

.

' put all this code in class a module and name the class module "ChkClass"

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

End Sub

.

' this code goes into a module

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("b2").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

Eureka! It works!!!! thank you so much! I added that tiny little code to the bottom of the class module to get column B to match column C whenever ANY of the checkboxes were clicked. God Bless You, sir! — csmith222, Oct 02 '17 at 01:35
I have another question about the next step with this code to make it work for my main purpose. I'm guessing the way i should go about this is to post it as a separate question and reference back to this page? — csmith222, Oct 03 '17 at 14:58

score 0 · Answer 2 · answered Oct 01 '17 at 22:22

0

All you need is to let EVERY checkbox's _Click() event know that you want to run the Worksheet_SelectionChange event. To do so you need to add the following line into every _Click() sub:

Call Worksheet_SelectionChange(Range("a1"))

Please note that it is irrelevant what range is passed to the SelectionChange sub since you do not use the Target in your code.

answered Oct 01 '17 at 22:22

Thank you for your effort to respond, but part of the idea was to not have to write code for each and every checkbox, since there will be over 100 on this sheet. My fingers tremble at the thought! it's a nice idea though. I'd upvote your answer, but apparently, I don't have enough reputation for this site to allow others to be subjected to my votes. :-/ – csmith222 Oct 02 '17 at 10:08

Event triggered by ANY checkbox click

2 Answers2

Linked

Related