Creating Checkboxes that when click uncheck the other

[mongoose]

I'm trying to create some checkboxes in a certain range. The range I have defined has sets of checkboxes next to each other; for example I5 & J5 is a basic yes or no. Same with I7 & J7 etc.
I'd like to make the checkboxes in each set uncheck each other. If yes is checked it will make sure no is unchecked, etc.
I'm having some trouble trying to assign the function with the .OnAction or .OnClick etc. Not sure what I am doing wrong here.

Public Sub Add_Checkboxes()


    'Delete all preexisting checkboxes
    For Each c In Worksheets(1).CheckBoxes
        c.Delete
    Next
    
    'Generate the checkboxes in a predefined range
    Dim rngChkBoxes As Range: Set rngChkBoxes = Range("I5, J5, I7, J7, I9, J9, I18, J18")
    For Each Rng In rngChkBoxes
        Worksheets(1).CheckBoxes.Add(Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height).Select
        
        With Selection
            .Caption = ""
            '.Name = "chk_"
            '.OnClick = IsChecked()
        End With
        
    Next Rng
End Sub


Public Sub IsChecked()
    MsgBox ("testing")
End Sub

[Leith Ross]

Hello mongoose,

When you need exclusive selection, one out of many, the control of choice is a Radio Button. Trying to alter the inherent functionality of a control when you don't need to is not a good use of your time, unless you just want to know how it is done.

[Artik]

When you need exclusive selection, one out of many, the control of choice is a Radio Button.

Holy words.

unless you just want to know how it is done.

And it gets done in this way:

Sub Add_Checkboxes()
    Dim c As Excel.CheckBox
    Dim rngChkBoxes As Range
    Dim Rng As Range
    
    'Delete all preexisting checkboxes
    For Each c In Worksheets(1).CheckBoxes
        c.Delete
    Next
    
    'Generate the checkboxes in a predefined range
    Set rngChkBoxes = Range("I5, J5, I7, J7, I9, J9, I18, J18")
    
    For Each Rng In rngChkBoxes
        Set c = Worksheets(1).CheckBoxes.Add(Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height)
        
        With c
            .Caption = ""
            .Name = "chk_" & IIf(Rng.Column = 9, "Yes", "No") & "_" & Rng.Row
            .OnAction = "IsChecked"
        End With
        
    Next Rng
End Sub



Private Sub IsChecked()
    Dim c As Excel.CheckBox
    Dim varName As Variant
    
    With ActiveSheet
    
        Set c = .CheckBoxes(Application.Caller)
        
        varName = Split(c.Name, "_")
        
        If varName(1) = "Yes" Then
            ActiveSheet.CheckBoxes("chk_No_" & varName(2)).Value = -4146
        Else
            ActiveSheet.CheckBoxes("chk_Yes_" & varName(2)).Value = -4146
        End If
    
    End With
End Sub

Artik

[p45cal]

When you need exclusive selection, one out of many, the control of choice is a Radio Button. Trying to alter the inherent functionality of a control when you don't need to is not a good use of your time, unless you just want to know how it is done.

Yes. OptionButtons (RadioButtons) (I'm talking the Form type, not the ActiveX type) will work as a group if enclosed in a GroupBox. Enclosing the OptionButtons in a GroupBox can be fraught as it seems that the GroupBox should completely enclose the OptionButtons - but I've found that still isn't always the case! Anyway here's a bit of code to add some OptionButtons, and a few (invisible) enclosing GroupBoxes on the active sheet:

Sub blah()
With ActiveSheet
  Set rngChkBoxes = .Range("I5,I7,I9,I18")    'only the first cell of each pair
  
  'Delete ALL existing Form groupboxes and Form optionbuttons on the sheet:
  For Each c In .OptionButtons
    c.Delete
  Next
  For Each c In .GroupBoxes
    c.Delete
  Next
    
  For Each cll In rngChkBoxes.Cells
    Set cll2 = cll.Offset(, 1)    'the second optionbutton location
    With .OptionButtons.Add(cll.Left, cll.Top, cll.Width, cll.Height)
      .Caption = ""
      .Height = cll.Height    'needed because it doesn't take on the correct height
    End With
    With .OptionButtons.Add(cll2.Left, cll2.Top, cll2.Width, cll2.Height)
      .Caption = ""
      .Height = cll2.Height    'needed because it doesn't take on the correct height
      '.LinkedCell = cll.Offset(, -1).Address(external:=True) 'this will also be the linked cell for all the option buttons in the same group.
    End With
    Set myRng = .Range(cll, cll2)
    With .GroupBoxes.Add(myRng.Left, myRng.Top, myRng.Width, myRng.Height)
      '.Caption = ""
      .Height = myRng.Height    'needed because it doesn't take on the correct height
      .Visible = False
    End With
  Next cll
End With
End Sub

[snb]

Simply so:

Sub M_snb()
  With Sheet1.OLEObjects
   For j = 1 To 5
     With .Add("Forms.OptionButton.1", , , , , , , 200, 100 + j * 36, 100, 24)
        .Name = "O_" & Format(j, "00")
        .Object.Caption = "Choice " & j
        .Object.groupname = "snb"
     End With
   Next
   End With
End Sub

But..., you'd better create these objects in Design Mode and make them invisible.
In real time you only have to make them visible by VBA code

[LarryG]

If I can intrude on the thread to ask some questions about the posts here, here they are; but I'll edit them away and put in another thread if asked:

I've always wondered how Excel knows whose option buttons are whose, but out of habit I've always just drawn a frame around them (or dragged them into a frame, actually). Is a GroupBox something different? [Disclaimer: the scope of my knowledge is what appears in the form Toolbox.] Anyway, I just tested a blank form with 1 option button, copied it twice, hit F5, and the three worked in concert (correctly) without anything to "group" them except the form boundary itself.

One more thing, is invisibility advantageous for the frame or Groupbox? It would seem easier to keep things straight if visible. I guess since it's visible in design, and can't be clicked on or tabbed to at runtime, it's not problematic; just wondering why you would though. (And I've actually deliberately made a invisible/transparent control before myself - as a trick to do a flyover message when moused over.)

And by the way, this whole discussion is about UserForm controls, rather than controls directly on a worksheet, right? If not, maybe ignore all my scribbling!

EDIT: I just drew a frame and dragged them in, but now I can't cycle around with the tab key. I can tab from button 1 to button 2 to button 3, but it stops there. Was I just wrong to use a frame at all?!

[p45cal]

this whole discussion is about UserForm controls, rather than controls directly on a worksheet, right?

Worksheet I'm afraid.

snb's solution adapted to fit more closely to the OP's requirements (these are ActiveX option buttons):

Sub blah2()
margin = 1 'small adjustment to size and placement of optionbuttons to keep sheet grid visible.
With ActiveSheet
  Set rngChkBoxes = .Range("I5,I7,I9,I18")    'only the first cell of each pair
  'Delete ALL existing activex optionbuttons on the sheet:
  For Each obj In .OLEObjects
    If TypeName(obj.Object) = "OptionButton" Then obj.Delete
  Next obj
  'Add new ones:
  For Each cll In rngChkBoxes.Cells
    Set cll2 = cll.Offset(, 1)    'the second optionbutton location
    With .OLEObjects.Add("Forms.OptionButton.1", , , , , , , cll.Left + margin, cll.Top + margin, cll.Width - 2 * margin, cll.Height - 2 * margin).Object
      .Caption = "Yes"
      .GroupName = "row" & Format(cll.Row, "000")
      .Font.Size = 9
    End With
    With .OLEObjects.Add("Forms.OptionButton.1", , , , , , , cll2.Left + margin, cll2.Top + margin, cll2.Width - 2 * margin, cll2.Height - 2 * margin).Object
      .Caption = "No"
      .GroupName = "row" & Format(cll.Row, "000")
      .Font.Size = 9
    End With
  Next cll
End With
End Sub

[Artik]

Much has already been said in this thread. I will only add one. Avoid ActiveX controls embedded in a worksheet. They will often be unstable.

Artik

[snb]

To illustrate what I mean:


@Artik

It's a matter of correctly defining the active-X's properties.

[Artik]

snb, can you tell me how to set the properties of the ActiveX control so that, for example, the ComboBox does not change its position on the sheet by itself or that it does not change its size when clicking on the control. Or that strange artifacts of these controls are created. These behaviors are unpredictable. Sometimes these go crazy, other times everything is OK. Because I meant such unstable behavior.

Artik

[snb]

I'd suggest:

Sub M_snb()
   For j = 0 To 1
     With Sheet1.OLEObjects.Add("Forms.Combobox.1", , , , , , , Columns(10).Left, Rows(4 + 2 * j).Top, 60, 18)
        .Name = "C_" & Format(j, "00")
        .Object.List = Split("A B C D E F G")
        .Placement = 3
     End With
    Next
End Sub

[Artik]

OK, let's see what life will show in the future.
The default property when inserting an object is xlMove.


Thx


Artik