Hello!
I have a worksheet with checkboxes. Each checkbox is linked to a cell. When 
checkboxes are checked/unchecked the cell changes, but the Change event for 
the sheet does NOT fire! Is that how it is supposed to be??? I wanted to use 
this method instead of Checkbox Click event, because I have lots of 
checkboxes. Also it seem to work before, but not anymore. Am I confusing 
something?

Please help! Thanks a lot in advance.

P.S: Alternativly I can have another cell with formula linked to the 
checkbox cell. This way Calculation event works. But it's not best practice :)

Lena,

If I use this in excel 2003 the change event is triggered:

Option Explicit
Dim enableEvents As Boolean

Private Sub CheckBox1_Click()
If enableEvents = True Then Exit Sub
enableEvents = True
Range("A1") = 2
enableEvents = False
End Sub

You don't say exactly what a checkbox being "linked" to a cell means.
Posting your code would be best.

This is from Chip Pearson's website:

"For the Change event in particular, it should be noted that this is
triggered when a cell is changed by user action or by other VBA code, but is
not raised if the value of a cell is changed as a result of formula
calculation."

HTH

"Lena" <Lena@discussions.microsoft.com> wrote in message
news:143C03B1-8C20-4F9D-B8EF-388663E832B1@microsoft.com...
> Hello!
> I have a worksheet with checkboxes. Each checkbox is linked to a cell.
When
> checkboxes are checked/unchecked the cell changes, but the Change event
for
> the sheet does NOT fire! Is that how it is supposed to be??? I wanted to
use
> this method instead of Checkbox Click event, because I have lots of
> checkboxes. Also it seem to work before, but not anymore. Am I confusing
> something?
>
> Please help! Thanks a lot in advance.
>
> P.S: Alternativly I can have another cell with formula linked to the
> checkbox cell. This way Calculation event works. But it's not best
practice :)

I don't have any code for checkbox itself. I use LinkedCell which is setup in 
the propeties of the checkbox and changes the value from TRUE/FALSE whenever 
the checkbox is checked/unchecked. I'm trying to catch an event when this 
cell changes it's value.
I just use a test code for now for Worksheet_Change event and it doesn not 
work when the cell is changed by the checkbox.

It does work however when I have another cell with the formula linking to my 
LinkedCell of the checkbox. Then I can use Worksheet_Calculate event. But I 
don't want to create another column just for that.

I have around 40 checkboxes and I don't want to use the Checkbox Click event 
for each of them.

"Project Mangler" wrote:

> Lena,
> 
> If I use this in excel 2003 the change event is triggered:
> 
> Option Explicit
> Dim enableEvents As Boolean
> 
> Private Sub CheckBox1_Click()
> If enableEvents = True Then Exit Sub
> enableEvents = True
> Range("A1") = 2
> enableEvents = False
> End Sub
> 
> You don't say exactly what a checkbox being "linked" to a cell means.
> Posting your code would be best.
> 
> This is from Chip Pearson's website:
> 
> "For the Change event in particular, it should be noted that this is
> triggered when a cell is changed by user action or by other VBA code, but is
> not raised if the value of a cell is changed as a result of formula
> calculation."
> 
> HTH
> 
> "Lena" <Lena@discussions.microsoft.com> wrote in message
> news:143C03B1-8C20-4F9D-B8EF-388663E832B1@microsoft.com...
> > Hello!
> > I have a worksheet with checkboxes. Each checkbox is linked to a cell.
> When
> > checkboxes are checked/unchecked the cell changes, but the Change event
> for
> > the sheet does NOT fire! Is that how it is supposed to be??? I wanted to
> use
> > this method instead of Checkbox Click event, because I have lots of
> > checkboxes. Also it seem to work before, but not anymore. Am I confusing
> > something?
> >
> > Please help! Thanks a lot in advance.
> >
> > P.S: Alternativly I can have another cell with formula linked to the
> > checkbox cell. This way Calculation event works. But it's not best
> practice :)
> 
> 
> .
> 

I'm not quite sure what you want to do, but instead of using 40 distinct _change
events, you could use a single _change event in a class module (for every
checkbox assigned to a specific group of checkboxes).

The "grouping" is done when the workbook opens.

This goes in a General module:

Option Explicit
Dim ChkBoxes() As New Class1
Sub Auto_Open()

    Dim CBXCount As Long
    Dim OLEObj As OLEObject
        
    CBXCount = 0
    For Each OLEObj In ThisWorkbook.Worksheets("sheet1").OLEObjects
        If TypeOf OLEObj.Object Is MSForms.CheckBox Then
            CBXCount = CBXCount + 1
            ReDim Preserve ChkBoxes(1 To CBXCount)
            Set ChkBoxes(CBXCount).CBXGroup = OLEObj.Object
        End If
    Next OLEObj
    
End Sub

And then when you're in the VBE, do Insert|Class Module
The name of this class module is Class1 (it's important to match what's in the
code):

Then paste this in the newly opened code window:

Option Explicit
Public WithEvents CBXGroup As MSForms.CheckBox
Private Sub CBXGroup_Change()
    With CBXGroup
        MsgBox .Name & vbLf & .Value
    End With
End Sub

You can read more info at John Walkenbach's site:
http://spreadsheetpage.com/index.php/file/multiple_userform_buttons_with_one_procedure



Lena wrote:
> 
> I don't have any code for checkbox itself. I use LinkedCell which is setup in
> the propeties of the checkbox and changes the value from TRUE/FALSE whenever
> the checkbox is checked/unchecked. I'm trying to catch an event when this
> cell changes it's value.
> I just use a test code for now for Worksheet_Change event and it doesn not
> work when the cell is changed by the checkbox.
> 
> It does work however when I have another cell with the formula linking to my
> LinkedCell of the checkbox. Then I can use Worksheet_Calculate event. But I
> don't want to create another column just for that.
> 
> I have around 40 checkboxes and I don't want to use the Checkbox Click event
> for each of them.
> 
> "Project Mangler" wrote:
> 
> > Lena,
> >
> > If I use this in excel 2003 the change event is triggered:
> >
> > Option Explicit
> > Dim enableEvents As Boolean
> >
> > Private Sub CheckBox1_Click()
> > If enableEvents = True Then Exit Sub
> > enableEvents = True
> > Range("A1") = 2
> > enableEvents = False
> > End Sub
> >
> > You don't say exactly what a checkbox being "linked" to a cell means.
> > Posting your code would be best.
> >
> > This is from Chip Pearson's website:
> >
> > "For the Change event in particular, it should be noted that this is
> > triggered when a cell is changed by user action or by other VBA code, but is
> > not raised if the value of a cell is changed as a result of formula
> > calculation."
> >
> > HTH
> >
> > "Lena" <Lena@discussions.microsoft.com> wrote in message
> > news:143C03B1-8C20-4F9D-B8EF-388663E832B1@microsoft.com...
> > > Hello!
> > > I have a worksheet with checkboxes. Each checkbox is linked to a cell.
> > When
> > > checkboxes are checked/unchecked the cell changes, but the Change event
> > for
> > > the sheet does NOT fire! Is that how it is supposed to be??? I wanted to
> > use
> > > this method instead of Checkbox Click event, because I have lots of
> > > checkboxes. Also it seem to work before, but not anymore. Am I confusing
> > > something?
> > >
> > > Please help! Thanks a lot in advance.
> > >
> > > P.S: Alternativly I can have another cell with formula linked to the
> > > checkbox cell. This way Calculation event works. But it's not best
> > practice :)
> >
> >
> > .
> >

-- 

Dave Peterson