I think you have a couple of choices...

1. Use checkboxes from the Forms toolbar and assign the same macro to each. In
fact, depending on what you're doing, you could use the checkbox's name and
maybe assign the same macro to all 6 checkboxes on each of the dozens of sheets.

2. If you're using checkboxes from the Control Toolbox toolbar, you may be able
to just use a single common subroutine in a General module--where each
checkbox's click event calls the common routine (passing the nice info to the
common routine).

3. Create a class module that "groups" all your optionbuttons (from the Control
toolbox toolbar) so that you can use a common procedure.

I'm gonna use the last one and it's based on the code on John Walkenbach's site:
http://j-walk.com/ss/excel/tips/tip44.htm

I created a new class module (called Class1) with this in it:

Option Explicit
Public WithEvents ChkBoxGroup As MSForms.CheckBox
Private Sub ChkBoxGroup_Click()

Dim mySFX As Long
Dim myAddresses() As Variant

myAddresses = Array("A1:b1", "e1:f1", "i1")

With ChkBoxGroup
If IsNumeric(Right(.Name, 1)) Then
mySFX = Right(.Name, 1)
Else
mySFX = 0
End If

Select Case mySFX
Case Is = 0
'do nothing!
Case 1 To 3 '3 addresses
.Parent.Range(myAddresses(mySFX - 1)).EntireColumn.Hidden _
= CBool(.Value = True)
End Select
End With

End Sub


The I added this to a general module:

Option Explicit
Dim ChkBoxes() As New Class1
Sub Auto_open()
Dim ChkBoxCtr As Long
Dim OLEObj As OLEObject
Dim wks As Worksheet

ChkBoxCtr = 0
For Each wks In ThisWorkbook.Worksheets
For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
ChkBoxCtr = ChkBoxCtr + 1
ReDim Preserve ChkBoxes(1 To ChkBoxCtr)
Set ChkBoxes(ChkBoxCtr).ChkBoxGroup = OLEObj.Object
End If
Next OLEObj
Next wks

End Sub


You have to make sure that the checkboxes are named nicely. Each sheet has to
have the checkboxes that end in a digit (1-6). I used checkbox1, checkbox2,
checkbox3 (I got lazy!).

This line:
myAddresses = Array("A1:b1", "e1:f1", "i1")
corresponded to those 3 checkboxes.

Checkbox1 controlled a:b
checkbox2 controlled e:f
checkbox3 controlled i (a single column)



Min wrote:

I have a workbook with a dozen worksheets. On each worksheet there are 6
checkboxes that I have made to hide or unhide different columns. The
checkboxes and the vb for them is exactly the same on each worksheet. Can I
have just one lot of code somewhere that will work on all the sheets, or have
I got to have the code written on each individual sheet, as I have at present?

--

Dave Peterson