A procedure should run when one cell switch between false/true - but not with worksheet_calculate or

[Gunnar Johansson]

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

 

[Doug Glancy]

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]

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]

You're welcome - I've been stuck in those trees too...

Doug