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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Tom Urtis

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

Watch MrExcel Video

Forum statistics

Threads
1,101,785
Messages
5,482,921
Members
407,367
Latest member
FunkyFriedChicken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top