Automatically Refer to Checkbox in CheckBox_Click()

[Kaiser86]

I have a table with 12 entries, and a checkbox next to each entry. The first row of this list shows "All".

What I am trying to do at the moment is to code the event triggers CheckBox(x)_Click so that when the checkbox next to "All" is checked, everything else is unchecked, and if any other checkbox is checked, the All would be unchecked.

Although it's pretty easy to hard code each and every event, I would like to have 1 set of code which can be just copied and pasted into a new checkbox event, in the case there's a new box added. This would allow easier changes within the list (e.g. decide that the "All" should be at the end of the list rather than the top), and would also make it easier to refer to the corresponding cell as Checkbox(x) refers to Cells(x,1) of the range.

At the moment I have:

Private Sub CheckBox1_Click()

If Range("TLList").Cells(1, 1) = "All" Then

   If CheckBox1.Value = True Then
       CheckBox10.Value = False
       CheckBox11.Value = False
       CheckBox12.Value = False
       CheckBox2.Value = False
       CheckBox3.Value = False
       CheckBox4.Value = False
       CheckBox5.Value = False
       CheckBox6.Value = False
       CheckBox7.Value = False
       CheckBox8.Value = False
       CheckBox9.Value = False
   End If



End If

End Sub

What I am asking is this:
If, for example, I'm coding in the event CheckBox1_Click, is there any way for refer to that Checkbox automatically? Something like Checkbox.This, which would refer to Checkbox1. And if it is possible, would I be able extract the "1" out of the name? I'm planning to use

ActiveSheet.Shapes("CheckBox" & i)

to loop around the rest.

 

[Gary McMaster]

I prefer to use a "Form" check box when it is necessary for all of the check boxes to access a common procedure. You can use "Application.Caller" to determine which one of the check boxes triggered the procedure.

Below is a sample you can try in a standard module in a new workbook.

I hope it helps.

Gary

Option Explicit

Sub CheckBox_Demo_Create()

Dim oShape As Shape
Dim oActive As Worksheet
Dim oCheckBox As CheckBox
Dim oRange As Range
Dim oCell As Range

Dim lWidth As Long
Dim lHeight As Long

lWidth = 100
lHeight = 12

Set oActive = ActiveSheet

Set oRange = oActive.Range("B1:B10")
oRange.ColumnWidth = 16
oRange.Offset(0, 1).ColumnWidth = 60

For Each oShape In oActive.Shapes
    If InStr(1, oShape.Name, "CheckBox") Then
        oShape.Delete
    End If
Next oShape

For Each oCell In oRange
    Set oCheckBox = oActive.CheckBoxes.Add(oCell.Left, oCell.Top, lWidth, lHeight)
    With oCheckBox
        '.LinkedCell = oCell.Address
        '.Interior.ColorIndex = 3
        .Name = "CheckBox_" & oCell.Address
        .Caption = .Name
        .OnAction = "Checkbox_Toggle"
    End With
Next oCell

End Sub

Public Sub CheckBox_Demo_Check_Uncheck()

Dim oShape As Shape
Dim oActive As Worksheet
Dim oCell As Range

Set oActive = ActiveSheet

For Each oShape In oActive.Shapes

If oShape.Type = msoFormControl Then
    If oShape.FormControlType = xlCheckBox Then
        If InStr(1, oShape.Name, "CheckBox") Then
            Set oCell = oShape.TopLeftCell
            If oShape.ControlFormat.Value = Checked Then
                oShape.ControlFormat.Value = Unchecked
            Else
                oShape.ControlFormat.Value = Checked
            End If
        End If
    End If
End If

Next oShape

End Sub

Public Sub Checkbox_Toggle()

Dim oShape As Shape
Dim oCell As Range
Dim oActive As Worksheet

Set oActive = ActiveSheet

Set oShape = oActive.Shapes(Application.Caller) 'Referenced as shape (see below)

Set oCell = oShape.TopLeftCell

If oShape.ControlFormat.Value = Checked Then
    oCell.Offset(0, 1).Value = "The check box in cell" & oCell.Address & " named " & oShape.Name & " was checked"
Else
    oCell.Offset(0, 1).Value = "The check box in cell" & oCell.Address & " named " & oShape.Name & " was un-checked"
End If


'If the name of the "Check Box" was changed after insertion it can no longer
'be reference by name as "Check Box" object. Use the following to retrieve
'the actual "Check Box" object by its index number

Dim oCheckBox As CheckBox
Dim iBoxCount As Integer

For iBoxCount = 1 To oActive.CheckBoxes.Count
    If oActive.CheckBoxes(iBoxCount).Name = Application.Caller Then
        Set oCheckBox = oActive.CheckBoxes(iBoxCount)
        Exit For
    End If
Next iBoxCount

If Not oCheckBox Is Nothing Then
    Debug.Print oCheckBox.TopLeftCell.Address
    Debug.Print oCheckBox.BottomRightCell.Address
    Debug.Print oCheckBox.Caption
    Debug.Print oCheckBox.Border.LineStyle
    Debug.Print oCheckBox.Interior.ColorIndex
    Debug.Print oCheckBox.Enabled
    Debug.Print oCheckBox.OnAction
    'whatever else
End If

End Sub

 

[Norie]

What type of checkboxes are they and where are they located?

Do they have a linked cell?