Activex Check Box VBA Code not working automatically

G

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.
Sort by date Sort by votes
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...

wellsr.com

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 wellsr.com

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

wellsr.com

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 wellsr.com

Hope this helps!
Click to expand...
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
ganeshadu said:
Thanks Dominic. I have 100s of Checkboxes in the form. Don't want to right code for each chec boxes. Any other suggestion please
Click to expand...
Apso I using the same code mentioned in the provided url. However it is not working automatically
 
Upvote 0
ganeshadu said:
I have 100s of Checkboxes in the form. Don't want to right code for each chec boxes. Any other suggestion please
Click to expand...
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
ganeshadu said:
Apso I using the same code mentioned in the provided url. However it is not working automatically
Click to expand...
I'm assuming that you've exited Design Mode (Ribbon >> Developer tab >> Controls group >> Design Mode button), right?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
228
bobsan42
bobsan42
C
  • Question
Adding Horizontal and/or Vertical borders with VBA.
Replies
0
Views
407
cr130
C
Agnarr
  • Solved
Checkbox Timestamp with VBA
Replies
3
Views
340
bobsan42
bobsan42
H
  • Solved
VBA to change color of Forms Control Button
Replies
6
Views
696
skillilea
S
M
  • Solved
Changing all checkboxes with no value to False: VBA Code
Replies
3
Views
860
Domenic
D

Forum statistics

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

Share this page

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