Make Check Boxes behave as Option buttons

Hello nikolab,

Welcome to the Forum!

I have to ask why you don't want to use option buttons. Option buttons are designed to be mutually exclusive - only one sets at a time. What type of form are you referring to: a worksheet or VBA user form?

If your checkboxes are not Active-X type, then I don't see how you can get this to work cleanly without a lot of messy code. I would delete them and use Option buttons.

If your check boxes are from the Active-X type controls then you could use the click event with code similar to this:

Option Explicit

Private Sub CheckBox1_Click()
    With Me
        If .CheckBox1.Value = True Then
            .CheckBox2.Value = False
            .CheckBox3.Value = False
        Else
            Exit Sub
        End If
    End With
End Sub

Here is a formula approach that might work for you, but it will take A LOT of manual work to get it set up.

Assume three check boxs linked to cells A1:A3
Using cells B1:B3 to contain a formula to test each other, you might use this approach:

B1: =IF(AND(A1=TRUE,OR(A2=TRUE,A3=TRUE)),"Make one Selection ONLY",A1)
B2: =IF(AND(A2=TRUE,OR(A1=TRUE,A3=TRUE)),"Make one Selection ONLY",A2)
B3: =IF(AND(A3=TRUE,OR(A1=TRUE,A2=TRUE)),"Make one Selection ONLY",A3)

It will not stop them from checking more than one box in the absolute sense, but it does return a message and can return a corect value when all is in order.

The help you even a bit further:

Sub add_opt()

Dim OptBut(1 To 9) As Control
Dim MyMod As Integer, GroupName As Integer

For i = 1 To 9
  MyMod = (i - 1) Mod 3 + 1  '1,2,3,  1,2,3,  1,2,3
  GroupNum = CInt(Application.WorksheetFunction.RoundDown((i - 1) / 3 + 1, 0))  '1,1,1,  2,2,2,  3,3,3
  Debug.Print MyMod, GroupNum
  Set OptBut(i) = UserForm1.Controls.Add("Forms.OptionButton.1", "OptBut" & i, True)
  With OptBut(i)
    .GroupName = GroupNum
    .Width = 20
    .Left = MyMod * (.Width + 2) + 10
    .Top = GroupNum * 15
  End With
Next i

End Sub

Given a useform, this will replace all check boxes with option buttons, preserving their postition and captions. Note that the name of the new option button is the same as the checkbox so that the event code does not need to be rewritten.

This should be run once at design time, it need not be run every time at runtime.

Sub CheckBoxesToOptionButtons()
    Dim myUF As Object
    Dim oneControl As Object
    Set myUF = ThisWorkbook.VBProject.vbcomponents("Userform1")
    
    With myUF.designer
        For Each oneControl In .Controls
            If TypeName(oneControl) = "CheckBox" Then
            Call OptionForCheck(oneControl, oneControl.Parent)
            End If
        Next oneControl
    End With
End Sub

Sub OptionForCheck(oldCheckBox As Object, cbParent As Object)
    Dim oName, oHeight, oWidth, oTop, oLeft, oCaption
    With oldCheckBox
        oName = .Name
        oHeight = .Height
        oWidth = .Width
        oTop = .Top
        oLeft = .Left
        oCaption = .Caption
    End With
    If TypeName(cbParent) = "Userform" Then Set cbParent = cbParent.designer
    
    With cbParent.Controls
        .Remove (oName)
        With .Add("Forms.OptionButton.1")
            .Name = oName
            .Caption = oCaption
            .Left = oLeft
            .Top = oTop
            .Height = oHeight
            .Width = oWidth
        End With
    End With
End Sub

Mike,

Nice code, looks good but for me this specific code

    Set myUF = ThisWorkbook.VBProject.VBComponents("Userform1")

does not seem to work. VBProject failed.
Any idea?

In the VBEditor Tools>References, is your Microsoft Visual Basic for Applications Extensibility library checked?

I'm still on my first cup of coffee, but I don't think that the code should crash there if the VBIDE isn't active, it should crash somewhere else.

Also, are you on a PC? I'm on a Mac and this code is right in the middle of differences between the platforms. It would be good to know if it works on both.

One quick update, I just tested the code on the Excel 2007 at work and it ran OK.