Activex Check Box VBA Code not working automatically

ganeshadu

New Member
Joined
Feb 20, 2024
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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...

VBA Code:
Private Sub CheckBox1_Click()
    'Your Macro Here
End Sub

Have a look at the following link...


Hope this helps!
 
Upvote 0
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...

VBA Code:
Private Sub CheckBox1_Click()
    'Your Macro Here
End Sub

Have a look at the following link...


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
 
Upvote 0
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
 
Upvote 0
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
VBA Code:
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top