Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A procedure should run when one cell switch between false/true - but not with worksheet_calculate or change event...
Hi,
Is there a way to start a procedure when a cells' value switch between false/true... without Worksheet_Calculate event?! There should be like a little "agent" looking at the cell (or the checkbox object) and every time it take False or True, a procedure can run. Worksheet_Change event is not either a solution, the target of action isn't that cell but a checkbox object, initiating the cell to set false or true value. Can you start a procedure through a contitional format function somehow, manybe? Now, I actually use Worksheet_Calculate event, but it doesn't work so good, because it seems like every time I make a change in any cell at all in the worksheet and start a worksheet_change event, the worksheet_calculate procedure seem to occure as well (Yes, I use the Application.EnableEvents = False). I can't understand why it does, I try to find the solution this way as well right know. I found some about recalculation in http://www.decisionmodels.com/calcsecrets.htm, but havn't been able to change it into code... / Kind Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A procedure should run when one cell switch between false/true - but not with worksheet_calculate or change event...
Gunnar,
I think you can get what you want by checking the checkbox, not the cell. For a checkbox from the Control Toolbox put this code in the code module for the sheet containing the checkbox (just double-click the checkbox in Design Mode and it will take you there. I assumed that the checkboxes are in Sheet1 for both examples: Private Sub CheckBox1_Click() If Worksheets("Sheet1").CheckBox1.Value = True Then MsgBox "True" ElseIf Worksheets("Sheet1").CheckBox1.Value = False Then MsgBox "False" End If End Sub For a checkbox from the Forms toolbar, assign the following macro and put the macro in a general module: Sub test() If Worksheets("Sheet1").CheckBoxes(Application.Caller ).Value = 1 Then MsgBox "True" ElseIf Worksheets("Sheet1").CheckBoxes(Application.Caller ).Value = -4146 Then MsgBox "False" End If End Sub (Question to the group: why can't I get True or False to work in the second module?) hth, Doug Glancy "Gunnar Johansson" wrote in message ... Hi, Is there a way to start a procedure when a cells' value switch between false/true... without Worksheet_Calculate event?! There should be like a little "agent" looking at the cell (or the checkbox object) and every time it take False or True, a procedure can run. Worksheet_Change event is not either a solution, the target of action isn't that cell but a checkbox object, initiating the cell to set false or true value. Can you start a procedure through a contitional format function somehow, manybe? Now, I actually use Worksheet_Calculate event, but it doesn't work so good, because it seems like every time I make a change in any cell at all in the worksheet and start a worksheet_change event, the worksheet_calculate procedure seem to occure as well (Yes, I use the Application.EnableEvents = False). I can't understand why it does, I try to find the solution this way as well right know. I found some about recalculation in http://www.decisionmodels.com/calcsecrets.htm, but havn't been able to change it into code... / Kind Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thank you, working excellent !
Thank you,
It's working fine. I wish I had thought of this earlier - I didn't see the forrest because of all trees in the way ... / Regards "Doug Glancy" skrev i meddelandet ... Gunnar, I think you can get what you want by checking the checkbox, not the cell. For a checkbox from the Control Toolbox put this code in the code module for the sheet containing the checkbox (just double-click the checkbox in Design Mode and it will take you there. I assumed that the checkboxes are in Sheet1 for both examples: Private Sub CheckBox1_Click() If Worksheets("Sheet1").CheckBox1.Value = True Then MsgBox "True" ElseIf Worksheets("Sheet1").CheckBox1.Value = False Then MsgBox "False" End If End Sub For a checkbox from the Forms toolbar, assign the following macro and put the macro in a general module: Sub test() If Worksheets("Sheet1").CheckBoxes(Application.Caller ).Value = 1 Then MsgBox "True" ElseIf Worksheets("Sheet1").CheckBoxes(Application.Caller ).Value = -4146 Then MsgBox "False" End If End Sub (Question to the group: why can't I get True or False to work in the second module?) hth, Doug Glancy "Gunnar Johansson" wrote in message ... Hi, Is there a way to start a procedure when a cells' value switch between false/true... without Worksheet_Calculate event?! There should be like a little "agent" looking at the cell (or the checkbox object) and every time it take False or True, a procedure can run. Worksheet_Change event is not either a solution, the target of action isn't that cell but a checkbox object, initiating the cell to set false or true value. Can you start a procedure through a contitional format function somehow, manybe? Now, I actually use Worksheet_Calculate event, but it doesn't work so good, because it seems like every time I make a change in any cell at all in the worksheet and start a worksheet_change event, the worksheet_calculate procedure seem to occure as well (Yes, I use the Application.EnableEvents = False). I can't understand why it does, I try to find the solution this way as well right know. I found some about recalculation in http://www.decisionmodels.com/calcsecrets.htm, but havn't been able to change it into code... / Kind Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thank you, working excellent !
You're welcome - I've been stuck in those trees too...
Doug "Gunnar Johansson" wrote in message ... Thank you, It's working fine. I wish I had thought of this earlier - I didn't see the forrest because of all trees in the way ... / Regards "Doug Glancy" skrev i meddelandet ... Gunnar, I think you can get what you want by checking the checkbox, not the cell. For a checkbox from the Control Toolbox put this code in the code module for the sheet containing the checkbox (just double-click the checkbox in Design Mode and it will take you there. I assumed that the checkboxes are in Sheet1 for both examples: Private Sub CheckBox1_Click() If Worksheets("Sheet1").CheckBox1.Value = True Then MsgBox "True" ElseIf Worksheets("Sheet1").CheckBox1.Value = False Then MsgBox "False" End If End Sub For a checkbox from the Forms toolbar, assign the following macro and put the macro in a general module: Sub test() If Worksheets("Sheet1").CheckBoxes(Application.Caller ).Value = 1 Then MsgBox "True" ElseIf Worksheets("Sheet1").CheckBoxes(Application.Caller ).Value = -4146 Then MsgBox "False" End If End Sub (Question to the group: why can't I get True or False to work in the second module?) hth, Doug Glancy "Gunnar Johansson" wrote in message ... Hi, Is there a way to start a procedure when a cells' value switch between false/true... without Worksheet_Calculate event?! There should be like a little "agent" looking at the cell (or the checkbox object) and every time it take False or True, a procedure can run. Worksheet_Change event is not either a solution, the target of action isn't that cell but a checkbox object, initiating the cell to set false or true value. Can you start a procedure through a contitional format function somehow, manybe? Now, I actually use Worksheet_Calculate event, but it doesn't work so good, because it seems like every time I make a change in any cell at all in the worksheet and start a worksheet_change event, the worksheet_calculate procedure seem to occure as well (Yes, I use the Application.EnableEvents = False). I can't understand why it does, I try to find the solution this way as well right know. I found some about recalculation in http://www.decisionmodels.com/calcsecrets.htm, but havn't been able to change it into code... / Kind Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What's the best way to toggle between true and false in Excel? Hiall, My excel work involves a lot of toggling between true and false (booleantypes) ... and it's very repetitive... Is there a way to select a bunch ofcells, and press a key short-cu | Excel Discussion (Misc queries) | |||
TRUE/FALSE BOX not activating a WS change | Excel Discussion (Misc queries) | |||
Validation Procedure with a worksheet change event | Excel Worksheet Functions | |||
Use IF to change value in a different cell when TRUE (or FALSE)? | Excel Discussion (Misc queries) | |||
change event procedure | Excel Programming |