Fire Macro from Cell Change

  • Thread starter Thread starter Slashman
  • Start date Start date
S

Slashman

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.
 
Carim said:
Hi,

To define your range with two cells :

Range("F27,K27")

HTH
Carim
Click to expand...

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.
 
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.
 
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
 
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.
 
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
 
Carim said:
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
Click to expand...

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.
 
Back
Top