excel macro check boxes

Question

I have the following code in a Macro in an excel spreadsheet

"Sub CheckBox2()

Dim xCheckBox As CheckBox

For Each xCheckBox In Application.ActiveSheet.CheckBoxes

If xCheckBox.Name <> Application.ActiveSheet.CheckBoxes("Check Box 2").Name Then

xCheckBox.Value = Application.ActiveSheet.CheckBoxes("Check Box 2").Value = False

End If

Next

End Sub"

There are 4 of these with check boxes and allows me to check one box and turn others off?

I want to create the same again but not to interfere with each other and they do????

Answer 1

Thankyou OssieMac, that worked, its really useful

Dan

Answer 2

Am I interpreting correctly that you want to have multiple groups of CheckBoxes and in each group when a box is checked you want to uncheck all other boxes in the same group without affecting the Checkboxes in other groups? If so then the following procedure and code.

You need to Group each set of CheckBoxes as follows.

• Right Click one check box to select it (The border and sizing handles should be visible)
• Hold the Ctrl key and then left click each other Checkbox required in the same group. (The border and sizing handles for all the checkboxes required in the group  should be visible)
• Right Click over one of the selected CheckBoxes and in the DropDown menu select Grouping -> Group.
• Repeat to create the remaining Groups.

Copy all of the code below into a standard module.

• Ensure that the worksheet with the CheckBoxes is the ActiveSheet.
• Change to the VBA editor (Alt/11) and Click anywhere within the Sub AssignMacro()
• Press F5 and it should run and assign the macro to be run whenever a Checkbox is clicked. This just saves you having to select each check box individually and assign the macro to be run when the checkbox is changed.
• You should only need to run this macro once unless you add more checkboxes and group them. If you do this then you can run the macro again so that it includes the new checkboxes (does not matter that it has already been run on the previous checkboxes because it will just set the macro to be called the same)
• Now when ever you click a Checkbox if the check mark visible (true) then the remaining checkboxes in the group will be false. You can remove the check mark from the checked box by clicking it.
• The code is generic in that it does not need to know the Checkbox Names or the Group Names. The code identifies the Checkbox name and the Parent Group.

Feel free to get back to me if any problems or questions relating to this.

Sub AssignMacro()

    'This sub will assign macro "UpdateChkBoxes" to all the CheckBoxes in each Group

    Dim shp As Shape

    Dim shpChk As Shape

    For Each shp In ActiveSheet.Shapes

        If shp.Type = msoGroup Then 'Ignore if not a Group

            For Each shpChk In shp.GroupItems   'Loop through shapes in Group

                If shpChk.FormControlType = xlCheckBox Then     'If CheckBox

                    shpChk.OnAction = "UpdateChkBoxes"          'Set macro to be called when CheckBox changes

                    shpChk.ControlFormat.Value = xlOff          'Initialize value of CheckBox to False (xlOff is False)

                End If

            Next shpChk

        End If

    Next shp

End Sub

Sub UpdateChkBoxes()

    Dim shpChk As Shape

    Dim chkBox As Shape

    Dim strGrpName As String

    Set chkBox = ActiveSheet.Shapes(Application.Caller) 'The CheckBox that called this sub

    strGrpName = chkBox.ParentGroup.Name

    'Next line: 'Code only runs if box is checked. If unchecked code does not process

    If chkBox.ControlFormat.Value = xlOn Then   'If checkBox checked

        'Next line: Loop through all of the CheckBoxes in the Group

        For Each shpChk In ActiveSheet.Shapes(strGrpName).GroupItems

            If shpChk.FormControlType = xlCheckBox Then 'Don't change any shape that is NOT a CheckBox

                If shpChk.Name <> chkBox.Name Then      'Don't change the Checked Box

                    shpChk.ControlFormat.Value = xlOff     'xlOff is False value

                End If

            End If

        Next

    End If

End Sub

Answer 3

If you use Optionbuttons instead you do not need any line of code. ;-)

Anyway, assign the macro below to each Checkbox.

Andreas.

Sub MyCheckBoxes_Click()

  Dim CB As CheckBox, ThisCB As CheckBox

  'Refer to the checkbox that called us

  Set ThisCB = ActiveSheet.CheckBoxes(Application.Caller)

  'Visit all CheckBoxes

  For Each CB In ActiveSheet.CheckBoxes

    'Turn all others off

    If CB.Name <> ThisCB.Name Then CB.Value = False

  Next

End Sub