Automatically Refer to Checkbox in CheckBox_Click()

Kaiser86

New Member
Joined
Oct 13, 2010
Messages
4
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:

Code:
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

Code:
ActiveSheet.Shapes("CheckBox" & i)
to loop around the rest.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
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

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,757
Office Version
365
Platform
Windows
What type of checkboxes are they and where are they located?

Do they have a linked cell?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,417
Messages
5,511,233
Members
408,831
Latest member
heidiussel

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top