Friday 5 January 2018

VBA - Sheet Checkboxes - Intrinsic and ActiveX

A SO question came up about accessing an ActiveX checkbox. Here is some code to programmatically access check-boxes. Intrinsic check-boxes are found via the Shapes collection whilst ActiveX check-boxes are found via the OLEObjects collection. Also, adding an ActiveX control adds a module level variable to the sheet module for best access. However, SO OP was having difficulty relying upon this.


Option Explicit



Friend Function IntrinsicCheckBox() As CheckBox
    '* use this if you inserted an intrinsic Excel Checkbox, default name Check Box 1
    Dim myMSFormsCheckbox As Object
    Set myMSFormsCheckbox = Me.Shapes.Item("Check Box 1")
    
    Dim chk As Object
    Set chk = myMSFormsCheckbox.OLEFormat.Object

    Dim chk2 As CheckBox
    Set IntrinsicCheckBox = chk

End Function


Friend Function ActiveXCheckBox() As MSForms.CheckBox
    '* use this if you inserted a ActiveX Checkbox
    Dim myActiveXCheckbox As OLEObject
    Set myActiveXCheckbox = Me.OLEObjects.Item("CheckBox1")
    
    Dim chk As MSForms.CheckBox
    Set chk = myActiveXCheckbox.Object

    Set ActiveXCheckBox = chk

End Function





No comments:

Post a Comment