Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to insert Check Boxes in each cell E2:E30 and F2:F30 where one box must be checked in either column E or column F in each row.

share|improve this question
    
Actually I would like 3 columns of check boxes. E2:E30, F2:F30 and G2:G30 where one box must be selected in each row. – ajc009 Jan 18 '13 at 0:31
    
You can insert a check box as a Form or as an ActiveX Control from the Developer tab on the Ribbon. What is the action you want to occur when the check boxes get checked (or not)? – chuff Jan 18 '13 at 1:18
    
"where one box must be selected in each row" - sounds more like an Option button group than checkboxes: just insert a "Group Box" (Developer tab->Insert->Form controls->Group box) and then three option buttons. They all link to the same cell, e.g. E2 and this will be either 1, 2 or 3. If you really need extra columns for each, you can compare it to this column, e.g. in F2: =E2=1, in G2: =E2=2, etc. – Peter Albert Jan 18 '13 at 7:33
    
@peter-albert why didn't you posting your answer below? It's a good answer and if posted below it can be chosen as the Accepted Answer for which you gain reputation. – CharlieRB Jan 18 '13 at 12:38
    
Yes I think the option button is a better choice as well. I used a visual basic module that I found online which worked well. The only issue was that I couldn't figure out how to edit the module to be more specific to what I needed it to do. – ajc009 Jan 18 '13 at 16:45

In your case, using Option Buttons is certainly the better/more pragmatic solution. If you need emulate an option group with check boxes, see further below

Option group

Manual way:

  1. Insert a Group Box (Developer tab->Insert->Form controls->Group box) - this is a rectangle in which you'll place the different option buttons
  2. Place the option buttons you need in the rectangle (Developer tab->Insert->Form controls->Option Button)
  3. Select either option button in your group and link it to the cell you which - either in the Format Control dialog (after right click) or by simple typing =$E$1 in the formula bar.

This linked cell will now hold the number of the option button that is pressed, in your case this could be 1, 2 or 3. You can now combine this with any other function e.g. INDEX/OFFSET/CHOOSE.

VBA way

The following routine will place an Option group next to each cell:

Private Const cStrPrefix As String = "o_"
Private Const cDblHorizontalSpacing As Double = 2
Private Const cDblLabelWidth As Double = 40

Private mWS As Worksheet
Private mStrAddr As String
Private mRngLink As Range
Private mVarLabels() As Variant
Private mIntCount As Integer

Public Sub subPlaceOptionGroupsInRange(rngLinks As Range, _
                         intNumberOfButtons As Integer, _
                         ParamArray varLabels() As Variant)

    Dim intOldCalcMode As Integer

    Application.ScreenUpdating = False
    intOldCalcMode = Application.Calculation
    Application.Calculation = xlCalculationManual

    'Init variables
    Set mWS = rngLinks.Worksheet
    mIntCount = intNumberOfButtons
    mVarLabels = varLabels

    For Each mRngLink In rngLinks.Cells
        mStrAddr = mRngLink.Address

        subDeleteOptionGroup
        subPlaceOptionGroup
        subPlaceOptionButtons

    Next

    Application.Calculation = intOldCalcMode
    Application.ScreenUpdating = True
End Sub

Private Sub subDeleteOptionGroup()
    Dim i As Integer

    On Error Resume Next
    For i = 1 To mIntCount
        mWS.OptionButtons(cStrPrefix & mStrAddr & "_" & i).Delete
    Next
    mWS.GroupBoxes(cStrPrefix & mStrAddr).Delete

End Sub

Private Sub subPlaceOptionGroup()
    Dim objGroupBox As GroupBox

    Set objGroupBox = mWS.GroupBoxes.Add( _
        mRngLink.Offset(, 1).Left, mRngLink.Top, _
        (mIntCount + 2) * cDblHorizontalSpacing + _
         mIntCount * cDblLabelWidth, _
        mRngLink.Height)
    With objGroupBox
        .Characters.Text = ""
        .Name = cStrPrefix & mStrAddr
        .Display3DShading = True
    End With

End Sub

Private Sub subPlaceOptionButtons()
    Dim i As Integer
    Dim objOptionButton As OptionButton
    For i = 1 To mIntCount
        Set objOptionButton = mWS.OptionButtons.Add( _
            mRngLink.Offset(, 1).Left _
            + i * cDblHorizontalSpacing + (i - 1) * cDblLabelWidth, _
            mRngLink.Top, cDblLabelWidth, mRngLink.Height)
        With objOptionButton
            .Characters.Text = mVarLabels(i - 1)
            .Display3DShading = True
            .Name = cStrPrefix & mStrAddr & "_" & i
            .LinkedCell = mStrAddr
        End With
    Next
End Sub

You can place your option buttons by running `subPlaceOptionGroupsInRange Sheets("yourSheet").Range("E2:E30"), 3, "Label1", "Label2", "Label3"

Checkboxes

If you want 3 checkboxes that are linked similar to the option group, you'll need a cell/column for each of the three buttons/options. In an example, cells A1,B1,C1 are linked to 3 checkboxes, which belong to one group.

To achieve the mutual exclusive state, you'll need to Assign the following macro to every option button:

Public Sub subChangeCheckbox()
    Dim cb As CheckBox
    Dim rngTarget As Range
    Dim intCol As Integer

    Set cb = ActiveSheet.CheckBoxes(Application.Caller)
    Set rngTarget = ActiveSheet.Range(cb.LinkedCell)

    'Prevent unchecking
    If rngTarget.Value = False Then
        rngTarget.Value = True
        Exit Sub
    End If

    'Unselect previously selected checkbox
    For intCol = 1 To 3
        If rngTarget.Column  intCol Then
            ActiveSheet.Cells(rngTarget.Row, intCol).Value = False
        End If
    Next intCol

End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .