Detecting changes to checkboxes via VBA

Question

Following on from my previous question.

A requirement from the customer is to have checkboxes on a report to disable rows of information on another sheet. The rows are defined as named ranges, formated by P_XXXXXX. The XXXXXX is a unique identifier that is also a field on the row so I can easily generate the range names on the fly.

The problem I am having is:

• After clicking on the items and then closing the form Excel asks if we want to save. This is undersirable.

I need someway of registering a change event happening on my generated checkboxes. So if one or more changes I can run through and hide/unhide the relevant ranges.

My code for adding the checkboxes looks like:

        ' For each row...

        ' check box in column 17(=Q).
        Dim lCenter As Long
        lCenter = rngCurrent.Width / 4 ' not actual centre but close enough
        With ActiveSheet.CheckBoxes.Add(rngCurrent.Left + lCenter, rngCurrent.Top - 2, rngCurrent.Width, rngCurrent.Height)
            .Interior.ColorIndex = xlNone
            .Caption = ""
        End With

So how do you link a change in a checkbox with a sub/function?

Answer 1

I don't think there are any events available with the Excel.Checkbox control. Try using the MSForms checkbox instead. You'll need a reference to 'Microsoft Forms 2.0 Object Library' - it's not redistributeable, but if you're using VBA, then that's fine.

You can then do something like this, and handle the event in the usual way:

''class level
Private WithEvents m_Checkbox as MSForms.CheckBox

Public Sub MakeCheckbox()
    Set m_Checkbox = Activesheet.OLEObjects.Add("Forms.Checkbox.1")
End Sub

Private Sub m_Checkbox_Click()
    ''Do stuff
End Sub

Obviously, you'll only be able to handle a set number of checkboxes this way - I would recommend creating a class to hold each checkbox.

Answer 2

Set the OnAction property of the Checkboxes object to the name of a sub you want to run whenever the checkbox is checked or unchecked.

Sub MakeCB()

    With ActiveSheet.CheckBoxes.Add(ActiveCell.Left + 0, ActiveCell.Top - 2, ActiveCell.Width, ActiveCell.Height)
        .Interior.ColorIndex = xlNone
        .Caption = ""
        .OnAction = "CheckboxChange"
    End With

End Sub

Sub CheckboxChange()

    MsgBox "change"

End Sub