I have placed about 30 to 40 Check boxes on All Sheets of my Excel Workbook. ( A

Question

I have placed about 30 to 40 Check boxes on All Sheets of my Excel Workbook. ( About 200 Sheets).

I need to Write a Common code for these check box of All sheets which executes the same results in all sheets on my workbook.

Check box names are common in all sheets. (i;e Checkbox1, Checkbox2, Checkbox3, ...........)

Example:

If CheckBox1 has some codes which executes the results in sheet1, I need the checkbox1 of sheet 2 to execute the same result in sheet2.

But I donot want to insert my codes in each sheets.

I tries pasting the codes in Workbook Module, but it didnot work.

If I paste the codes in Individual Sheets, then the Check Box Works,

I have the following codes.

Private Sub CheckBox11_Click()
If CheckBox11.Value = True Then Range("A60").Value = 0
If CheckBox11.Value = False Then Range("A60").Value = 1
End Sub

Private Sub CheckBox13_Click()
If CheckBox13.Value = True Then Range("A61").Value = 0
If CheckBox13.Value = False Then Range("A61").Value = 1
End Sub

Private Sub CheckBox15_Click()
If CheckBox15.Value = True Then Range("A62").Value = 0
If CheckBox15.Value = False Then Range("A62").Value = 1
End Sub

Private Sub CheckBox17_Click()
If CheckBox17.Value = True Then Range("A63").Value = 0
If CheckBox17.Value = False Then Range("A63").Value = 1
End Sub

Private Sub CheckBox19_Click()
If CheckBox19.Value = True Then Range("A64").Value = 0
If CheckBox19.Value = False Then Range("A64").Value = 1
End Sub

Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then Range("A65").Value = 0
If CheckBox2.Value = False Then Range("A65").Value = 1
End Sub

Private Sub CheckBox21_Click()
If CheckBox21.Value = True Then Range("A66").Value = 0
If CheckBox21.Value = False Then Range("A66").Value = 1
End Sub

Private Sub CheckBox23_Click()
If CheckBox23.Value = True Then Range("A67").Value = 0
If CheckBox23.Value = False Then Range("A67").Value = 1
End Sub

Private Sub CheckBox25_Click()
If CheckBox25.Value = True Then Range("A68").Value = 0
If CheckBox25.Value = False Then Range("A68").Value = 1
End Sub

Private Sub CheckBox27_Click()
If CheckBox27.Value = True Then Range("A69").Value = 0
If CheckBox27.Value = False Then Range("A69").Value = 1
End Sub

Private Sub CheckBox29_Click()
If CheckBox29.Value = True Then Range("A70").Value = 0
If CheckBox29.Value = False Then Range("A70").Value = 1
End Sub

Private Sub CheckBox31_Click()
If CheckBox31.Value = True Then Range("A71").Value = 0
If CheckBox31.Value = False Then Range("A71").Value = 1
End Sub

Private Sub CheckBox33_Click()
If CheckBox33.Value = True Then Range("A72").Value = 0
If CheckBox33.Value = False Then Range("A72").Value = 1
End Sub

Private Sub CheckBox35_Click()
If CheckBox35.Value = True Then Range("A73").Value = 0
If CheckBox35.Value = False Then Range("A73").Value = 1
End Sub


Private Sub CheckBox37_Click()
If CheckBox37.Value = True Then Range("A74").Value = 0
If CheckBox37.Value = False Then Range("A74").Value = 1
End Sub

Private Sub CheckBox38_Click()
If CheckBox38.Value = True Then Range("A75").Value = 0
If CheckBox38.Value = False Then Range("A75").Value = 1
End Sub

Private Sub CheckBox4_Click()
If CheckBox4.Value = True Then Range("A76").Value = 0
If CheckBox4.Value = False Then Range("A76").Value = 1
End Sub

Private Sub CheckBox40_Click()
If CheckBox40.Value = True Then Range("A77").Value = 0
If CheckBox40.Value = False Then Range("A77").Value = 1
End Sub

Private Sub CheckBox42_Click()
If CheckBox42.Value = True Then Range("A78").Value = 0
If CheckBox42.Value = False Then Range("A78").Value = 1
End Sub

Private Sub CheckBox6_Click()
If CheckBox6.Value = True Then Range("A79").Value = 0
If CheckBox6.Value = False Then Range("A79").Value = 1
End Sub

Private Sub CheckBox8_Click()
If CheckBox8.Value = True Then Range("A80").Value = 0
If CheckBox8.Value = False Then Range("A80").Value = 1
End Sub

Help!!

Explanation / Answer

Sub Test()
Dim n As Integer, x As Integer, pos As Integer
Dim nm As String, txt As String

With ActiveSheet.Shapes(Application.Caller)
    x = .ControlFormat.Value
    If x <> 1 Then x = 0
    txt = .TextFrame.Characters.Text 'just a demo on how to get caption instead of name
    nm = .Name
End With

pos = InStrRev(nm, " ")
n = CInt(Right$(nm, Len(nm) - pos))

n = Int((n - 11) / 2)

Cells(n + 60, 1).Value = x

End Sub

In the above code, "Check Box 11" effects cell A60 and "Check Box 13" effects cell A61.

Example of how to programmatically apply the same macro to all check boxes on all sheets in case the sheets are already made. I would actually set up the check boxes on one worksheet and just make multiple copies instead of using the below code. This won't work with ActiveX type check boxes.

Sub CBoxSetup()
Dim cb As Shape
Dim ws As Excel.Worksheet

For Each ws In ThisWorkbook.Worksheets
    For Each cb In ws.Shapes
        If cb.Type = msoFormControl Then
            If cb.FormControlType = xlCheckBox Then
                cb.OnAction = "Test"
            End If
        End If
    Next
Next

Set cb = Nothing: Set ws = Nothing
End Sub

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects