I am trying to fix a questionairre that has about 60
checkboxes. When any of the checkboxes are clicked, to on
or off, I want a particular macro to run.

I have tried using the worksheet change with the target
being the linked cells that the user has for each
checkbox, but this does not kick in.

Can anyone suggest anything?

thanks
George

Hi,

If you have used checkboxes from the Forms toolbar, you
can attach a (the same!) macro to each of them.

Regards,

Jan Karel Pieterse
Excel TA/MVP

-----Original Message-----
I am trying to fix a questionairre that has about 60
checkboxes. When any of the checkboxes are clicked, to
on
or off, I want a particular macro to run.

I have tried using the worksheet change with the target
being the linked cells that the user has for each
checkbox, but this does not kick in.

Can anyone suggest anything?

thanks
George
.

If from the control toolbox toolbar, then you can try John Walkenbach's
method:

http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

The example is for commandbuttons, but the technique would be the same for
checkboxes and other MSForms 2.0 controls (activeX controls).

--
Regards,
Tom Ogilvy


"George J" wrote in message
...
I am trying to fix a questionairre that has about 60
checkboxes. When any of the checkboxes are clicked, to on
or off, I want a particular macro to run.

I have tried using the worksheet change with the target
being the linked cells that the user has for each
checkbox, but this does not kick in.

Can anyone suggest anything?

thanks
George

Thanks guys - i was using checkboxes from the control
toolbox onto the worksheet.

I almost have it figured out, but keep running extra
times - not sure how to fix it without disabling the
checkboxes.

In a class module i have

Option Explicit

Public WithEvents cb As MSForms.CheckBox


Private Sub cb_Click()
Dim ctl As Object

If cb.Value Then
MsgBox "call macro"

End If
End Sub

and in a normal module

Option Explicit

Public col As New Collection

Sub CheckBoxes()
Dim cbSheet As Class2
Dim ctl As OLEObject

For Each ctl In Sheet1.OLEObjects
If TypeOf ctl.Object Is MSForms.CheckBox Then
Set cbSheet = New Class2
Set cbSheet.cb = ctl.Object
col.Add cbSheet
End If
Next ctl
End Sub

I should be able to work this out but my brain is fried.
Any advice most welcome.

thanks again
George

-----Original Message-----
If from the control toolbox toolbar, then you can try
John Walkenbach's
method:

http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

The example is for commandbuttons, but the technique
would be the same for
checkboxes and other MSForms 2.0 controls (activeX
controls).

--
Regards,
Tom Ogilvy


"George J" wrote in
message
...
I am trying to fix a questionairre that has about 60
checkboxes. When any of the checkboxes are clicked, to
on
or off, I want a particular macro to run.

I have tried using the worksheet change with the target
being the linked cells that the user has for each
checkbox, but this does not kick in.

Can anyone suggest anything?

thanks
George


.

I tested your code on a worksheet with 3 checkboxes and it worked fine. If
you are getting multiple runs, I suspect you have linked your checkboxes to
cells and perhaps a calculate is causing the macro to run (because the
calculation causes the checkboxes to update). Try unlinking the cells and
writing to the cells with code.

--
Regards,
Tom Ogilvy

wrote in message
...
Thanks guys - i was using checkboxes from the control
toolbox onto the worksheet.

I almost have it figured out, but keep running extra
times - not sure how to fix it without disabling the
checkboxes.

In a class module i have

Option Explicit

Public WithEvents cb As MSForms.CheckBox


Private Sub cb_Click()
Dim ctl As Object

If cb.Value Then
MsgBox "call macro"

End If
End Sub

and in a normal module

Option Explicit

Public col As New Collection

Sub CheckBoxes()
Dim cbSheet As Class2
Dim ctl As OLEObject

For Each ctl In Sheet1.OLEObjects
If TypeOf ctl.Object Is MSForms.CheckBox Then
Set cbSheet = New Class2
Set cbSheet.cb = ctl.Object
col.Add cbSheet
End If
Next ctl
End Sub

I should be able to work this out but my brain is fried.
Any advice most welcome.

thanks again
George

-----Original Message-----
If from the control toolbox toolbar, then you can try
John Walkenbach's
method:

http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

The example is for commandbuttons, but the technique
would be the same for
checkboxes and other MSForms 2.0 controls (activeX
controls).

--
Regards,
Tom Ogilvy


"George J" wrote in
message
...
I am trying to fix a questionairre that has about 60
checkboxes. When any of the checkboxes are clicked, to
on
or off, I want a particular macro to run.

I have tried using the worksheet change with the target
being the linked cells that the user has for each
checkbox, but this does not kick in.

Can anyone suggest anything?

thanks
George


.

Thanks for the suggestion Tom.

Dr Watson started having an argument with Excel and I had
to reboot.

On opening the workbook again it all worked perfectly.

Thanks for the time and advice.

regards
George
-----Original Message-----
I tested your code on a worksheet with 3 checkboxes and
it worked fine. If
you are getting multiple runs, I suspect you have linked
your checkboxes to
cells and perhaps a calculate is causing the macro to run
(because the
calculation causes the checkboxes to update). Try
unlinking the cells and
writing to the cells with code.

--
Regards,
Tom Ogilvy

Hi George,
You need a Class Module.
Have a look at this example at John Walkenbach's site;
http://j-walk.com/ss/excel/tips/tip49.htm

It implements a colour picker dialog box. Basically, when you hit any
of 56 different buttons it runs the same macro. You should be able to
adapt the code for your requirements without too much difficulty, and
it's quite enjoyable getting it to work!
If you can get it, have a look at Excel 2000 VBA (2002 by now??) by
John Green et al, Chapter 13 on Class Modules too.

regards
Paul


"George J" wrote in message ...
I am trying to fix a questionairre that has about 60
checkboxes. When any of the checkboxes are clicked, to on
or off, I want a particular macro to run.

I have tried using the worksheet change with the target
being the linked cells that the user has for each
checkbox, but this does not kick in.

Can anyone suggest anything?

thanks
George