Prev: When this NG migrates to forums, please consider ...
Next: Can use VSTO to reduce the size of a file?
From: Lena on 7 Jun 2010 14:06
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 :)
From: Project Mangler on 7 Jun 2010 14:48
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(a)discussions.microsoft.com> wrote in message
news:143C03B1-8C20-4F9D-B8EF-388663E832B1(a)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 :)


From: Lena on 7 Jun 2010 16:13
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(a)discussions.microsoft.com> wrote in message
> news:143C03B1-8C20-4F9D-B8EF-388663E832B1(a)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 :)
>
>
> .
>
From: Dave Peterson on 7 Jun 2010 17:16
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(a)discussions.microsoft.com> wrote in message
> > news:143C03B1-8C20-4F9D-B8EF-388663E832B1(a)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
 | 
Pages: 1
Prev: When this NG migrates to forums, please consider ...
Next: Can use VSTO to reduce the size of a file?