Change Event with Check Box?

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?

Yes, use the Change event of the checkbox.

--
Regards,
Tom Ogilvy


"John" wrote:

> 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?

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?

"Tom Ogilvy" wrote:

> Yes, use the Change event of the checkbox.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "John" wrote:
>
> > 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?

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

--
Regards,
Tom Ogilvy


"John" wrote:

> 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?
>
> "Tom Ogilvy" wrote:
>
> > Yes, use the Change event of the checkbox.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "John" wrote:
> >
> > > 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?

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

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "John" wrote:
>
> > 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?
> >
> > "Tom Ogilvy" wrote:
> >
> > > Yes, use the Change event of the checkbox.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "John" wrote:
> > >
> > > > 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?

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.

twogilvy@msn.com

--
Regards,
Tom Ogilvy

"John" wrote:

> 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
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "John" wrote:
> >
> > > 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?
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Yes, use the Change event of the checkbox.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "John" wrote:
> > > >
> > > > > 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?

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


"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "John" wrote:
>
> > 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?
> >
> > "Tom Ogilvy" wrote:
> >
> > > Yes, use the Change event of the checkbox.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "John" wrote:
> > >
> > > > 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?