Hello,
I have 40 check boxes and I would like the following routine to be performed
when a check box is ticked.

Private Sub CheckBox1_Click()
Range(Range("Parameter1"), Range("Parameter1").Offset(0, 4)).Copy
Range("ParameterInput").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

if checkbox1 is clicked I would like to copy the value from Parameter1, when
checkbox2 is clicked I would like to copy the value from Parameter2, and so
on.

is there an elegant way to do this without repeating 40 times the code?
thanks
--
caroline

This example uses commandbuttons on a userform, but you can do the same with
your situation: (view this as a technique/approach)

http://www.j-walk.com/ss/excel/tips/tip44.htm

--
Regards,
Tom Ogilvy


"caroline" wrote:

Hello,
I have 40 check boxes and I would like the following routine to be performed
when a check box is ticked.

Private Sub CheckBox1_Click()
Range(Range("Parameter1"), Range("Parameter1").Offset(0, 4)).Copy
Range("ParameterInput").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

if checkbox1 is clicked I would like to copy the value from Parameter1, when
checkbox2 is clicked I would like to copy the value from Parameter2, and so
on.

is there an elegant way to do this without repeating 40 times the code?
thanks
--
caroline

Thanks but I am not sure this can work in my case. I have other controls than
the checkboxes on my sheet.
any other idea?
thanks
--
caroline


"Tom Ogilvy" wrote:

This example uses commandbuttons on a userform, but you can do the same with
your situation: (view this as a technique/approach)

http://www.j-walk.com/ss/excel/tips/tip44.htm

--
Regards,
Tom Ogilvy


"caroline" wrote:

Hello,
I have 40 check boxes and I would like the following routine to be performed
when a check box is ticked.

Private Sub CheckBox1_Click()
Range(Range("Parameter1"), Range("Parameter1").Offset(0, 4)).Copy
Range("ParameterInput").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

if checkbox1 is clicked I would like to copy the value from Parameter1, when
checkbox2 is clicked I would like to copy the value from Parameter2, and so
on.

is there an elegant way to do this without repeating 40 times the code?
thanks
--
caroline

No need for other ideas. It doesn't make any difference about other
controls. You write the macro to only add the controls you want to be
handled. Simple ways to implement this are to name the controls so the ones
to be handled have a unique text string in their name as an example. Then
when you loop throught the oleObjects collection, you only set up those
controls to be handled.



' Other ideas are writing multiple events.

--
Regards,
Tom Ogilvy


"caroline" wrote:

Thanks but I am not sure this can work in my case. I have other controls than
the checkboxes on my sheet.
any other idea?
thanks
--
caroline


"Tom Ogilvy" wrote:

This example uses commandbuttons on a userform, but you can do the same with
your situation: (view this as a technique/approach)

http://www.j-walk.com/ss/excel/tips/tip44.htm

--
Regards,
Tom Ogilvy


"caroline" wrote:

Hello,
I have 40 check boxes and I would like the following routine to be performed
when a check box is ticked.

Private Sub CheckBox1_Click()
Range(Range("Parameter1"), Range("Parameter1").Offset(0, 4)).Copy
Range("ParameterInput").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

if checkbox1 is clicked I would like to copy the value from Parameter1, when
checkbox2 is clicked I would like to copy the value from Parameter2, and so
on.

is there an elegant way to do this without repeating 40 times the code?
thanks
--
caroline