Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event does not fire
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 :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event does not fire
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" wrote in message ... 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 :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event does not fire
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" wrote in message ... 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 :) . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event does not fire
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..._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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Sheet causes Combo Box change event to fire on original sheet | Excel Programming | |||
Why does change event fire when workbookclosed | Excel Programming | |||
Essbase Causing Selection Change Event to Fire | Excel Programming | |||
Fire Event only when Cell Change? | Excel Programming | |||
How do I get Pivot filter change event to fire? | Excel Programming |