Activex Check Box VBA Code not working automatically

[ganeshadu]

Thanks for welcoming in this forum.

Created Excel sheet in Office 365 Enterprise edition with both Form Control and ActiveX Check Boxes and Macro to change the colour when selected. When selected the Form Control check box colors are automatically getting changed, whereas for Activex need to run the macro manually everytime to change the colour. Please assist to change the color for Activex as well automatically. Below are the codes which i am using for both. Added the below codes in module.

For ActiveX

Sub CheckboxLoop()
Dim objX As OLEObject
With ActiveSheet
For Each objX In .OLEObjects
If TypeName(objX.Object) = "CheckBox" Then
If objX.Object.Value = True Then
objX.Object.BackColor = RGB(0, 255, 0)
Else
objX.Object.ForeColor = RGB(0, 0, 0)
objX.Object.BackColor = RGB(255, 255, 255)
End If
End If
Next
End With
End Sub


For Form Control

Sub SetMacro()
Dim CB
For Each CB In ActiveSheet.CheckBoxes
If CB.OnAction = "" Then CB.OnAction = "CheckedUnchecked"
Next CB
End Sub


Sub CheckedUnchecked()
With ActiveSheet.Shapes(Application.Caller).DrawingObject
If .Value = 1 Then
.Interior.Color = RGB(0, 255, 0)
Else
.Interior.Color = RGB(255, 255, 255)
End If
End With
End Sub

 

[Domenic]

To run a macro when an ActiveX CheckBox is clicked, you'll need to use the Click event. And you'll need to place the event hanlder in the code module for the sheet. So, for example, let's say that you've added a CheckBox to Sheet1, and that the CheckBox is named CheckBox1. Right-click the sheet tab for Sheet1, select View Code, and paste the following code in the sheet's code module...

Private Sub CheckBox1_Click()
    'Your Macro Here
End Sub

Have a look at the following link...

The Complete Guide to Excel VBA ActiveX Checkboxes - wellsr.com

This complete guide shows you how to control Excel ActiveX Checkboxes using VBA. The guide includes macros that return the value, check and uncheck the boxes.

wellsr.com

Hope this helps!

 

[ganeshadu]

To run a macro when an ActiveX CheckBox is clicked, you'll need to use the Click event. And you'll need to place the event hanlder in the code module for the sheet. So, for example, let's say that you've added a CheckBox to Sheet1, and that the CheckBox is named CheckBox1. Right-click the sheet tab for Sheet1, select View Code, and paste the following code in the sheet's code module...

Private Sub CheckBox1_Click()
    'Your Macro Here
End Sub

Have a look at the following link...

The Complete Guide to Excel VBA ActiveX Checkboxes - wellsr.com

This complete guide shows you how to control Excel ActiveX Checkboxes using VBA. The guide includes macros that return the value, check and uncheck the boxes.

wellsr.com

Hope this helps!

Thanks Dominic. I have 100s of Checkboxes in the form. Don't want to right code for each chec boxes. Any other suggestion please

 

[ganeshadu]

Thanks Dominic. I have 100s of Checkboxes in the form. Don't want to right code for each chec boxes. Any other suggestion please

Apso I using the same code mentioned in the provided url. However it is not working automatically

 

[NoSparks]

I have 100s of Checkboxes in the form. Don't want to right code for each chec boxes. Any other suggestion please

I suspect someone will do this with a class module, but if all else fails
you could use a macro to write to a text file and just copy and paste the subs to the sheet module.
Something like this might be of use

Sub WriteCheckboxSubsToFile()

    Dim strPath As String, strCode As String
    Dim fso As Object, oFile As Object
    Dim objX As OLEObject
    
    With ActiveSheet
        For Each objX In .OLEObjects
            If TypeName(objX.Object) = "CheckBox" Then
                strCode = strCode & "Private Sub " & objX.Name & "_Click()" & vbCrLf & _
                "   Call CheckboxLoop" & vbCrLf & "End Sub" & vbCrLf
            End If
        Next objX
    End With
    
    strPath = "D:\Misc_Excel_Stuff\ChkBoxCode.txt"      '<~~ change to your location
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFile = fso.CreateTextFile(strPath)
    
    oFile.WriteLine strCode
    oFile.Close
    Set fso = Nothing
    Set oFile = Nothing

End Sub

 

[Domenic]

Apso I using the same code mentioned in the provided url. However it is not working automatically

I'm assuming that you've exited Design Mode (Ribbon >> Developer tab >> Controls group >> Design Mode button), right?

 

[ganeshadu]

I'm assuming that you've exited Design Mode (Ribbon >> Developer tab >> Controls group >> Design Mode button), right?

Yes Dominic.