Making Check Boxes Behave Like Radio Buttons
Unlike radio buttons, check boxes don't cycle through with only one box being checked. Sometimes it is desirable to allow only a single check box while turning off all other check boxes in the same group. The code to accomplish that is shown below. Note that the Application.EnableEvents = False doesn't work for this type of control change.
Program Code
Option Explicit
Dim intCount As Integer
Private Sub CheckBox1_Click()
If intCount <> 0 Then
Exit Sub
End If
intCount = 1
Me.CheckBox2.Value = False
Me.CheckBox3.Value = False
intCount = 0
End Sub
Private Sub CheckBox2_Click()
If intCount <> 0 Then
Exit Sub
End If
intCount = 1
Me.CheckBox1.Value = False
Me.CheckBox3.Value = False
intCount = 0
End Sub
Private Sub CheckBox3_Click()
If intCount <> 0 Then
Exit Sub
End If
intCount = 1
Me.CheckBox1.Value = False
Me.CheckBox2.Value = False
intCount = 0
End Sub
Creating And Manipulating Active-X Checkboxes
This code segment shows to to create, modify and access properties of Active-X Checkboxes.
Program Code
Option Explicit
' ***************************************************************
' Check To See The Type of Active-X OLE Object
' THIS FIRST SUB ONLY WORKS IN FORMS - NOT WORKSHEETS
' ***************************************************************
Sub ManipulateActiveXCheckboxes()
Dim wksCheckBoxes As Worksheet
Dim o As OLEObject
Set wksCheckBoxes = Sheets("Short Sleeve Attributes Page")
For Each o In wksCheckBoxes.OLEObjects
If TypeOf o.Object Is MSForms.CheckBox Then
o.Object.Value = False
End If
Next o
End Sub
' ***************************************************************
' Other Manipulation Methods
' ***************************************************************
Option Explicit
Sub ManipulateActiveXCheckboxes()
Dim oleObj As OLEObject
Dim intCount As Integer
Dim sh As Worksheet
Dim s As Shape
Dim o As OLEObject
Set sh = ActiveSheet
Dim obj As OLEObject
Set oleObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=Range("C10").Left, _
Top:=Range("C10").Top, Width:=108, Height:=19.5)
With oleObj.Object
.Caption = "Click Me"
.Font.Size = 10
.Font.Italic = True
.Font.Bold = True
.Value = xlOff
End With
intCount = sh.OLEObjects.Count
Debug.Print "OLE Objects = " & intCount
For Each o In sh.OLEObjects
If o.progID = "Forms.CheckBox.1" Then
Debug.Print o.Name
Debug.Print o.OLEType
Debug.Print o.Object.Value
Debug.Print o.Object.Caption
o.Object.Caption = "Hello"
o.Object.Value = xlOn
Debug.Print o.Object.Caption
Debug.Print o.Object.Value
Debug.Print o.TopLeftCell.Address
End If
Next o
Set sh = Nothing
End Sub
