none
Detecting When Checkbox Changes RRS feed

  • Question

  • I have numerous ActiveX checkboxes on a sheet.  I placed them with code.  I link the checkbox to a cell so the cell says TRUE when it is checked and FALSE when it is unchecked.  Works perfectly.  Code to link cell:

        ws.OLEObjects(ckName).LinkedCell = "" & ws.Cells(i + STRROW, 8).Address(RowAbsolute:=True, ColumnAbsolute:=True)

    On the sheet I detect when the sheet changes:

    Private Sub Worksheet_Change(ByVal Target As Range)

    I have the following code below to detect any linked cell changes.  When the Checkbox changes the linked cell it doesn't detect the change but if I manually change the linked cell it works fine. 

      Set ws = ActiveSheet
      Set ckCells = Range(ws.Cells(STR_ROW, c.ck), ws.Cells(END_ROW, c.ck))
     
    
     If Not Application.Intersect(ckCells, Range(Target.Address)) Is Nothing Then
        sumExp = 0
        For i = STRROW To ENDROW
          ckName = "PE" & i
          If ws.Cells(i, c.ck) Then
            sumExp = sumExp + ws.Cells(i, c.Exp)
          End If
        Next i
        ws.Cells(10, 3) = sumExp
      End If

    I'm open to any ideas on to detect when a checkbox changes.  I tried OnAction but that didn't work. 

    Wednesday, June 6, 2018 11:14 PM

Answers

  • If you've got activeX checkboxes a WithEvents class should have no problem to capture events

    ' worksheet module
    Option Explicit
    Private cbxEvents() As Class1 ' rename Class1 something meaningfull
    
    Private Sub Worksheet_Activate()
    Dim i As Long
    Dim ole As OLEObject
        ReDim cbxEvents(1 To Me.OLEObjects.Count + 1)
        For Each ole In Me.OLEObjects
            If TypeName(ole.Object) = "CheckBox" Then
                i = i + 1
                Set cbxEvents(i) = New Class1
                Set cbxEvents(i).cbx = ole.Object
            End If
        Next
        If i < UBound(cbxEvents) Then
            ReDim Preserve cbxEvents(1 To i)
        End If
    End Sub
    
    ' Class1
    Public WithEvents cbx As MSForms.CheckBox
    
    Private Sub cbx_Change()
        With cbx.Parent.Range(cbx.LinkedCell)
            .Offset(0, 1) = .Value
        End With
    End Sub
    
    ' normal module
    Sub test()
    ' cell A7 is a linked cell
        With Range("A7")
            .Value = IIf(.Value, False, True)
        End With
    End Sub

    The code is written for brevity, probably wouldn't want to get things running in the sheet's activate event, the redim preserve not ideal, might want to add the checkbox as a Property rather than 'public', error handling, etc

    But it works. Define cell A7 as a linked cell for one of your checkboxes and run test, or change the checkbox manually. Either way cell B7 should update in the WithEvents checkbox change event with the new value of the linked cell

    • Marked as answer by mogulman52 Sunday, June 10, 2018 1:26 PM
    Thursday, June 7, 2018 4:48 PM
    Moderator

All replies

  • I figured out there is no generic way to capture a checkbox change event.  I tried a Class with WithEvents on checkbox but it didn't work.  The only way to do it is to have a sub for each checkbox:

    Private Sub CheckBox1_Click()
        'Your Macro Here
    End Sub

    So if you have 60 checkboxes you have 60 subs.

    Thursday, June 7, 2018 3:32 PM
  • If you've got activeX checkboxes a WithEvents class should have no problem to capture events

    ' worksheet module
    Option Explicit
    Private cbxEvents() As Class1 ' rename Class1 something meaningfull
    
    Private Sub Worksheet_Activate()
    Dim i As Long
    Dim ole As OLEObject
        ReDim cbxEvents(1 To Me.OLEObjects.Count + 1)
        For Each ole In Me.OLEObjects
            If TypeName(ole.Object) = "CheckBox" Then
                i = i + 1
                Set cbxEvents(i) = New Class1
                Set cbxEvents(i).cbx = ole.Object
            End If
        Next
        If i < UBound(cbxEvents) Then
            ReDim Preserve cbxEvents(1 To i)
        End If
    End Sub
    
    ' Class1
    Public WithEvents cbx As MSForms.CheckBox
    
    Private Sub cbx_Change()
        With cbx.Parent.Range(cbx.LinkedCell)
            .Offset(0, 1) = .Value
        End With
    End Sub
    
    ' normal module
    Sub test()
    ' cell A7 is a linked cell
        With Range("A7")
            .Value = IIf(.Value, False, True)
        End With
    End Sub

    The code is written for brevity, probably wouldn't want to get things running in the sheet's activate event, the redim preserve not ideal, might want to add the checkbox as a Property rather than 'public', error handling, etc

    But it works. Define cell A7 as a linked cell for one of your checkboxes and run test, or change the checkbox manually. Either way cell B7 should update in the WithEvents checkbox change event with the new value of the linked cell

    • Marked as answer by mogulman52 Sunday, June 10, 2018 1:26 PM
    Thursday, June 7, 2018 4:48 PM
    Moderator
  • I finally got it working.  I immediately recognized my error from your example.  MANY, MANY thanks for the solution.
    Sunday, June 10, 2018 1:28 PM