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


"Bob" wrote in message ...
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 CheckBox2
If .Value = True Then
Worksheets(2).Range("A9:C9").Value =
ActiveSheet.Range("A9:C9").Value
Else
Worksheets(2).Range("A9:C9").Value = ""
End If
End With
End Sub

Please help! Thank You

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" wrote in message ..
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

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 a
hideoleobjects, viewoleobjects,clearform,unprotectregion,showallun protectedcells.....

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.


"Bob" wrote in message ...
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. Bob

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

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


"Bob" wrote in message ...
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 CheckBox2
If .Value = True Then
Worksheets(2).Range("A9:C9").Value =
ActiveSheet.Range("A9:C9").Value
Else
Worksheets(2).Range("A9:C9").Value = ""
End If
End With
End Sub
Please help! Thank You

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

Bob wrote:

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. Bob

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

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


"Bob" wrote in message ...
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 CheckBox2
If .Value = True Then
Worksheets(2).Range("A9:C9").Value =
ActiveSheet.Range("A9:C9").Value
Else
Worksheets(2).Range("A9:C9").Value = ""
End If
End With
End Sub
Please help! Thank You


--

Dave Peterson

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
-----Original Message-----
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 CheckBox2
If .Value = True Then
Worksheets(2).Range("A9:C9").Value =
ActiveSheet.Range("A9:C9").Value
Else
Worksheets(2).Range("A9:C9").Value = ""
End If
End With
End Sub

Please help! Thank You

.