Change Event with Check Box?

[Guest]

I want to fire code based on a check box being checked or unchecked. The
worksheet change event doesn't seem to recognise the True/False changes when
I check box is checked. My code is simple.

Private Sub Worksheet_Change(ByVal Target As Range)
'If Not Application.Intersect(Range("i31:i60"), Target) Is Nothing Then
If Not Intersect(Target, Range("i31:i60")) Is Nothing Then
MsgBox "test"
End If
End Sub

Where column I contains true or false as a cell value based on the checkbox.
Can I get this to fire off of check boxes themselves?

 

[Guest]

Yes, use the Change event of the checkbox.

 

[Guest]

Thanks Tom, can this be done for the entire list of checkboxes? and would
you mind giving an example of the change event for a or any check box in the
sheet?

 

[Guest]

Private Sub CheckBox1_Change()
msgbox "Test - CheckBox1")
End Sub

If you want to handle all checkboxes with a single event, you could use a
technique documented by John Walkenbach:

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

 

[Guest]

Thanks again, I have two questions.

1. One what does this code do... here is my attempted modification
2. Where do I insert a Call to do what I want to happen when one of the 20
or so checkboxes is checked?

Thanks so much...

 

[Guest]

the code....

Option Explicit
Dim CBX() As New Class1

Sub ShowDialog()
Dim CBXCount As Integer
Dim ctl As Control

' Create the Button objects
CBXCount = 0
For Each ctl In ActiveSheet.Controls
If TypeName(ctl) = "Checkbox" Then
If ctl.Name <> "OKButton" Then 'Skip the OKButton
CBXCount = CBXCount + 1
ReDim Preserve CBX(1 To CBXCount)
Set CBX(CBXCount).checkboxGroup = ctl
End If
End If
Next ctl
'UserForm1.Show
End Sub

 

[Guest]

send me a sample file pertinent to the situation (not some huge thing) that
represents the critical aspects. Tell me what you want to happend when a
checkbox is checked or unchecked. In clude the code you want to run or an
explanation. Have the checkboxes on the sheet.

I will set it up for you as an example.

(e-mail address removed)