Is there any way that a macro can be activated when the data of a specific
cell changes?

I assigned a macro to a "check box" button from the Tool Bar "Forms", and
used the "cell link" from the "check box" properties, so when the data
changes in the cell linked the "check box" is marked and the macro will
start. So far, the "check box" gets marked when data changes but the macro
doesn't start. The only way the macro starts is when I click in the "check
box".

Maybe you could use a worksheet event that will fire when the cell's value
changes.

If it's changed by someone's typing...

I put this code in a general module and assigned the procedure to the checkbox.

Option Explicit
Sub testme(Optional myCBX As CheckBox)

If myCBX Is Nothing Then
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
End If

If myCBX.Value = xlOn Then
MsgBox "It's on"
Else
MsgBox "It's off"
End If

End Sub

Then behind the worksheet module, I put this code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCBX As CheckBox

Set myCBX = Worksheets("sheet1").CheckBoxes("check box 1")

If Intersect(Target, Me.Range(myCBX.LinkedCell)) Is Nothing Then
Exit Sub
End If

Call testme(myCBX)

End Sub

Maybe it'll give you a few ideas you can use.

jwwjd wrote:

Is there any way that a macro can be activated when the data of a specific
cell changes?

I assigned a macro to a "check box" button from the Tool Bar "Forms", and
used the "cell link" from the "check box" properties, so when the data
changes in the cell linked the "check box" is marked and the macro will
start. So far, the "check box" gets marked when data changes but the macro
doesn't start. The only way the macro starts is when I click in the "check
box".

--

Dave Peterson

I use this code when I want to fire a macro by changing the data in a
specific cell (in my case, cell E4. And the macro fired is called "Update")

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E4")) Is Nothing Then Application.Run
"Update"

Rightclick sheet tab, view code. Paste this stuff in there. Make sure you
select the cell you want, and the macro you want.

"Dave Peterson" wrote in message
...
Maybe you could use a worksheet event that will fire when the cell's value
changes.

If it's changed by someone's typing...

I put this code in a general module and assigned the procedure to the
checkbox.

Option Explicit
Sub testme(Optional myCBX As CheckBox)

If myCBX Is Nothing Then
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
End If

If myCBX.Value = xlOn Then
MsgBox "It's on"
Else
MsgBox "It's off"
End If

End Sub

Then behind the worksheet module, I put this code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCBX As CheckBox

Set myCBX = Worksheets("sheet1").CheckBoxes("check box 1")

If Intersect(Target, Me.Range(myCBX.LinkedCell)) Is Nothing Then
Exit Sub
End If

Call testme(myCBX)

End Sub

Maybe it'll give you a few ideas you can use.

jwwjd wrote:

Is there any way that a macro can be activated when the data of a
specific
cell changes?

I assigned a macro to a "check box" button from the Tool Bar "Forms",
and
used the "cell link" from the "check box" properties, so when the data
changes in the cell linked the "check box" is marked and the macro will
start. So far, the "check box" gets marked when data changes but the
macro
doesn't start. The only way the macro starts is when I click in the
"check
box".

--

Dave Peterson

Dave Peterson's suggestion worked out smoothly, but only when the data is
changed manually. Is there a way of doing the same but when the value is
affected by a formula? according to the result of the formula (eg. true or
false)


"jwwjd" wrote:

Is there any way that a macro can be activated when the data of a specific
cell changes?

I assigned a macro to a "check box" button from the Tool Bar "Forms", and
used the "cell link" from the "check box" properties, so when the data
changes in the cell linked the "check box" is marked and the macro will
start. So far, the "check box" gets marked when data changes but the macro
doesn't start. The only way the macro starts is when I click in the "check
box".

So you have a linked cell that's updated by a formula, too.

If you click the checkbox, doesn't the formula go away?

(This sounds like it could be quite a problem to me.)

Anyway, this worked for me.

The checkbox is on Sheet1 (which has a codename of Sheet1) that has a linked
cell of A1.

This is the code behind Sheet1:

Option Explicit
Public OldA1Value As Variant
Sub Worksheet_Calculate()

Dim myCBX As CheckBox

If Me.Range("a1").Value = OldA1Value Then
'do nothing
Else
OldA1Value = Me.Range("a1").Value
Set myCBX = Sheet1.CheckBoxes("check box 1")
Call testme(myCBX)
End If

End Sub

This is the code behind ThisWorkbook:

Option Explicit
Private Sub Workbook_Open()
Sheet1.OldA1Value = Sheet1.Range("a1").Value
End Sub

(when the workbook opens, it gets the initial value of the range)

And this is in a General module:

Option Explicit
Sub testme(Optional myCBX As CheckBox)

If myCBX Is Nothing Then
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
End If

If myCBX.Value = xlOn Then
MsgBox "It's on"
Else
MsgBox "It's off"
End If

End Sub

=====
But it still sounds like a problem if you ever click on that checkbox.

jwwjd wrote:

Dave Peterson's suggestion worked out smoothly, but only when the data is
changed manually. Is there a way of doing the same but when the value is
affected by a formula? according to the result of the formula (eg. true or
false)

"jwwjd" wrote:

Is there any way that a macro can be activated when the data of a specific
cell changes?

I assigned a macro to a "check box" button from the Tool Bar "Forms", and
used the "cell link" from the "check box" properties, so when the data
changes in the cell linked the "check box" is marked and the macro will
start. So far, the "check box" gets marked when data changes but the macro
doesn't start. The only way the macro starts is when I click in the "check
box".


--

Dave Peterson

Thank you very much Dave Peterson. It is amazing how many things excel can do
with the proper programing. Thanks for your help. This descussion room is
great!!!

I'm using the check box just to activate the macro; but your are right once
I click in the box my formula is override by either False or true. I'm
thinking of no touching the check box.
Thanks again
jwwjd

"Dave Peterson" wrote:

So you have a linked cell that's updated by a formula, too.

If you click the checkbox, doesn't the formula go away?

(This sounds like it could be quite a problem to me.)

Anyway, this worked for me.

The checkbox is on Sheet1 (which has a codename of Sheet1) that has a linked
cell of A1.

This is the code behind Sheet1:

Option Explicit
Public OldA1Value As Variant
Sub Worksheet_Calculate()

Dim myCBX As CheckBox

If Me.Range("a1").Value = OldA1Value Then
'do nothing
Else
OldA1Value = Me.Range("a1").Value
Set myCBX = Sheet1.CheckBoxes("check box 1")
Call testme(myCBX)
End If

End Sub

This is the code behind ThisWorkbook:

Option Explicit
Private Sub Workbook_Open()
Sheet1.OldA1Value = Sheet1.Range("a1").Value
End Sub

(when the workbook opens, it gets the initial value of the range)

And this is in a General module:

Option Explicit
Sub testme(Optional myCBX As CheckBox)

If myCBX Is Nothing Then
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
End If

If myCBX.Value = xlOn Then
MsgBox "It's on"
Else
MsgBox "It's off"
End If

End Sub

=====
But it still sounds like a problem if you ever click on that checkbox.

jwwjd wrote:

Dave Peterson's suggestion worked out smoothly, but only when the data is
changed manually. Is there a way of doing the same but when the value is
affected by a formula? according to the result of the formula (eg. true or
false)

"jwwjd" wrote:

Is there any way that a macro can be activated when the data of a specific
cell changes?

I assigned a macro to a "check box" button from the Tool Bar "Forms", and
used the "cell link" from the "check box" properties, so when the data
changes in the cell linked the "check box" is marked and the macro will
start. So far, the "check box" gets marked when data changes but the macro
doesn't start. The only way the macro starts is when I click in the "check
box".


--

Dave Peterson