Code for many check boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can this code be written for 10 - 20 check boxes in a row that do the same thing
So I do not have to write the code 10 - 20 times for each box

Private Sub CheckBox2_Click(
With CheckBox
If .Value = True The
Worksheets(2).Range("A9:C9").Value
ActiveSheet.Range("A9:C9").Valu
Els
Worksheets(2).Range("A9:C9").Value = "
End I
End Wit
End Su

Please help! Thank Yo
 
Bob,
AFAIK, each checkbox must have its own click sub. You
can cut and paste the guts from each program or call
a common sub or function if each checkbox does substantially
the same thing.

Regards,
Doug
 
Doug,
How do you write the common sub or function? Each check box range increases by one is the only difference
Thank you very much for your help. Bo

----- Doug Broad wrote: ----

Bob
AFAIK, each checkbox must have its own click sub. Yo
can cut and paste the guts from each program or cal
a common sub or function if each checkbox does substantiall
the same thing

Regards
Dou
 
Bob
Doug is right and I can't imagine that they do exactly the
same thing on the same set of cells. If that were the case
and you wanted them all you could group them.

Alternatively, you can copy the macro, select it and use
Find/Replace function on the edit menu (It works just like
Excel or Word) and change the check box number. That
should cut down the work involved and reduce mistakes.

Regards
Peter
 
Sub mysub (arg1 as whatever, arg2 as whatever....)
do whatever with the args
end

To call the sub

call mysub(stuff, morestuff,...)

By passing the sub the addresses or ranges that you want
the click event to change you can reduce the verbiage of
checking and clearing any other checkboxes you need.

Some sub names I have used for helping with forms are:
hideoleobjects, viewoleobjects,clearform,unprotectregion,showallunprotectedcells.....

If each function is completely different from the rest, then
calling subfunctions will not help.

Some concrete examples:

Private Sub CheckBox14_Click()
If CheckBox14 Then
toggleOff "Checkbox", 9, 10, 11, 12, 13, 15, 16, 17
Range("o31").Value = "III-B"
Else: Range("o31").ClearContents
End If
End Sub

Private Sub toggleOff(typ As String, ParamArray lst() As Variant)
For Each i In lst
OLEObjects.Item(typ & i).Object.Value = False
Next i
manageSpecOcc 0
End Sub


Hope that helps.
 
The original post has disappeared (for me).

But as an alternative, the OP could use checkboxes from the Forms toolbar. Each
of those could be assigned the same macro.

I'm not sure how the code varies for each checkbox, but you can determine which
checkbox called the macro with code like:

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

MsgBox myCBX.TopLeftCell.Address '.row or .column will work, too.

End Sub
 
Back
Top