Checkboxes Yes No

barim

Board Regular
Joined
Apr 19, 2006
Messages
167
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.
Code:
Set myCBX1 = wks.CheckBoxes.Add _
      (Top:=.Top, Width:=.Width, _
       Height:=.Height, Middle:=.Middle)
Here is the whole code:
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,803
Office Version
2007
Platform
Windows
You can achieve an effect like the following, but using OptionButtons



Run this macro:

Code:
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
 

Forum statistics

Threads
1,077,955
Messages
5,337,391
Members
399,144
Latest member
Lauren Ward

Some videos you may like

This Week's Hot Topics

Top