Checkboxes Yes No

[barim]

Hello,

I have the following code that I picked up from some web page. This one inserts only one button to the left of the cell.
I would like to have two checkboxes in one cell, one for "Yes" another for "No" and only one can be checked.
I tried to replicate second option by adding "1" to the variable. Why "Middle" or "Right" doesn't work?
Thanks.

Set myCBX1 = wks.CheckBoxes.Add _
      (Top:=.Top, Width:=.Width, _
       Height:=.Height, Middle:=.Middle)

Here is the whole code:

Sub AddCheckBoxesRange()
'by Dave Peterson
'add Form checkboxes
Dim c As Range
Dim myCBX As CheckBox
Dim myCBX1 As CheckBox
Dim wks As Worksheet
Dim rngCB As Range
Dim rngCB1 As Range
Dim strCap As String
Dim strCap1 As String

Set wks = ActiveSheet
Set rngCB = wks.Range("J23:J26")
Set rngCB1 = wks.Range("J23:J26")
'Set rngCB = Selection
strCap = "YES"
strCap1 = "NO"

For Each c In rngCB
  With c
    Set myCBX = wks.CheckBoxes.Add _
      (Top:=.Top, Width:=.Width, _
       Height:=.Height, Left:=.Left)
  End With
  With myCBX
    .Name = "cbx_" & c.Address(0, 0)
    .LinkedCell = c.Offset(23, 10) _
        .Address(external:=True)
    .Caption = strCap
'    .OnAction = ThisWorkbook.Name _
'        & "!mycbxMacro"
  End With
Next c

For Each c In rngCB1
  With c
    Set myCBX1 = wks.CheckBoxes.Add _
      (Top:=.Top, Width:=.Width, _
       Height:=.Height, Middle:=.Middle)
  End With
  With myCBX1
    .Name = "cbx1_" & c.Address(0, 0)
    .LinkedCell = c.Offset(23, 10) _
        .Address(external:=True)
    .Caption = strCap1
'    .OnAction = ThisWorkbook.Name _
'        & "!mycbxMacro"
  End With
Next c

End Sub

 

[Tom Urtis]

Seeing as only a Yes or a No can be indicated, why not just have 1 option button in the cell, because that is the purpose an option button serves. If it is selected, it evaluates to True (yes); otherwise if not selected, it evaluates to False (no).

 

[DanteAmor]

You can achieve an effect like the following, but using OptionButtons

Run this macro:

Sub AddOptionButtons()
  Dim btn1 As OptionButton, btn2 As OptionButton, grbox As GroupBox
  Dim sh As Worksheet, r As Range, c As Range
  Set sh = ActiveSheet
  Set r = sh.Range("J23:J26")
  sh.OptionButtons.Delete
  sh.GroupBoxes.Delete
  For Each c In r
    Set btn1 = sh.OptionButtons.Add(c.Left + 1, c.Top + 1, 30, c.Height - 2)
    Set btn2 = sh.OptionButtons.Add(c.Left + 31, c.Top + 1, 30, c.Height - 2)
    Set grbox = sh.GroupBoxes.Add(c.Left - 2, c.Top - 2, c.Width + 6, c.Height + 6)
      btn1.Caption = "Yes"
      btn1.LinkedCell = c.Offset(, 10).Address(external:=True)
      btn2.Caption = "No"
      grbox.Caption = ""
      grbox.Visible = False
  Next
End Sub