Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.