Fire Macro from Cell Change
 

Hi,

I have got this code from another thread in my search for a solution to
a cell firing a macro.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Not Intersect(Target, Range("f27")) Is Nothing Then
For Each myCell In Intersect(Target, Range("f27"))
If myCell.Value < 90 Then
Call Rockwell_AddC
End If
If myCell.Value = " " Then
Call Rockwell_RemoveC
End If
If k27 < 90 And f27 < 90 Then
MsgBox "Fire" 'Call Rockwell_Both
End If
Next myCell
End If
End Sub

I have a third condition (Rockwell_both) that I want to be able to call
when both k27 and f27 have a number in them less than 90, but it seems
to only see the f27 as the keycell as determined by the Target, Range
at the beginning of the code.

I have put a MSGBox in to debug, but rem out the actual macro call.

Can anyone help with this one?

Cheers,

Aaron.

Fire Macro from Cell Change
 

Hi,

To define your range with two cells :

Range("F27,K27")

HTH
Carim

Fire Macro from Cell Change
 

Carim wrote:
Hi,

To define your range with two cells :

Range("F27,K27")

HTH
Carim

Hi,

This just makes both cells the keycells, I need a condition to fire a
macro when both cells are changed and when each cell is changed
individually. IE 3 conditions.

Cheers,

Aaron.

Fire Macro from Cell Change
 

Hi,

Is there possibly a different more efficient way to do this?

I could just put a button on the page and press it manually, but it
would be nice for this to happen automatically if I can. I might have
to have a button there for now though until I can get a solution.

Cheers,

Aaron.
Slashman wrote:
Carim wrote:
Hi,

To define your range with two cells :

Range("F27,K27")

HTH
Carim

Hi,

This just makes both cells the keycells, I need a condition to fire a
macro when both cells are changed and when each cell is changed
individually. IE 3 conditions.

Cheers,

Aaron.

Fire Macro from Cell Change
 

Hi,


If I may suggest something :
first step is to clarify your matrix of 9 possibilities to determine
macro to be called ...
then, you will adopt the most efficient path ...

HTH
Carim

Fire Macro from Cell Change
 

Hi,

Yes I have 3 possible conditions.

One is cell f27 changes from " " to anything but it will be numerical,
it fires Rockwell_AddC
One is cell k27 changes from " " to anything but it will be numerical,
it fires Rockwell_RemoveC
and the last condition is if both keycells change, it fires macro
Rockwell_both

Cheers,

Aaron.


Carim wrote:
Hi,


If I may suggest something :
first step is to clarify your matrix of 9 possibilities to determine
macro to be called ...
then, you will adopt the most efficient path ...

HTH
Carim

Fire Macro from Cell Change
 

Hi,

Have you tried to insert, at the very begining of your event macro :
Application.EnableEvents = False
and
Application.EnableEvents = True
at the very end of your macro

Carim

Fire Macro from Cell Change
 

Carim wrote:
Hi,

Have you tried to insert, at the very begining of your event macro :
Application.EnableEvents = False
and
Application.EnableEvents = True
at the very end of your macro

Carim

Hi, do you mean like this?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Not Intersect(Target, Range("f27")) Is Nothing Then
For Each myCell In Intersect(Target, Range("f27"))
If myCell.Value < 90 Then
Application.EnableEvents = False
Call Rockwell_AddC
Application.EnableEvents = True
End If
If myCell.Value = " " Then
Application.EnableEvents = False
Call Rockwell_RemoveC
Application.EnableEvents = True
End If
If (k27 < 90 And f27 < 90) Then
Application.EnableEvents = False
Call Rockwell_Both
Application.EnableEvents = True
End If
Next myCell
End If
End Sub

It still doesent work. It fires Rockwell_Both on contents either k27 or
f27 changing.

Cheers,

Aaron.