Common Coding of ActiveX Check boxes for All Sheets. Hi All, I have placed about

Question

Common Coding of ActiveX Check boxes for All Sheets.

Hi All,

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 Ranege("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 Rangee("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 Rangee("A80").Value = 0
If CheckBox8.Value = False Then Range("A80").Value = 1
End Sub

Explanation / Answer

First, I have to ask: You say you have 30 to 40 check boxes on a total of 200 (?) worksheets. That would total 6000 to 8000 check boxes!!! Did you actually mean to say 20 worksheets?

Hans' suggestion would be the first approach. In case you get into trouble with too many ActiveX controls, you might want to go with native Forms check boxes instead. You can assign a single macro to all of them and extract the numeric value from the name of the calling check box. Then use this number as an offset to determine which cell to control.

Forms controls have spaces in their names (e.g "Check Box 22"). So I would use InStrRev to get the position of the last space and extract the numeric portion using it.

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 Exxcel.Worksheet

For Each ws In ThisWorkboook.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: Seet ws = Nothing
End Sub

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.