barim

Board Regular
Joined
Apr 19, 2006
Messages
176
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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).
 
Upvote 0
You can achieve an effect like the following, but using OptionButtons

3fe9578139a6b5b0cea3f73d5d9cf41b.jpg


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
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,775
Members
448,298
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top