Tri state macro
 

Hi,

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

It works well, but I cant get the third condition to work IE Call
Rockwell Both.

Perhaps it can be done a totally different way also.

I want cell f27 changing to fire a macro A

Cell k27 changing to fire a macro B

and if both cells change to fire macro C


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
Call Rockwell_Both
End If
Next myCell
End If
End Sub

Any advice greatly appreciated.

Cheers,

Aaron.

Tri state macro
 

Change

If k27 < 90 And f27 < 90 Then

to

If Target("K27").Value < 90 And Target.Value < 90 Then


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Aaron" wrote in message
oups.com...
Hi,

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

It works well, but I cant get the third condition to work IE Call
Rockwell Both.

Perhaps it can be done a totally different way also.

I want cell f27 changing to fire a macro A

Cell k27 changing to fire a macro B

and if both cells change to fire macro C


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
Call Rockwell_Both
End If
Next myCell
End If
End Sub

Any advice greatly appreciated.

Cheers,

Aaron.

Tri state macro
 

maybe??? be sure you use caps for your $A instead of $a

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$10" And Target < 90 Then a
If Target.Address = "$A$11" And Target < 90 And Cells(10, "a") < 90 Then
c
ElseIf Target.Address = "$A$11" And Target < 90 Then
b
End If
End Sub

Sub a()
MsgBox "a"
End Sub
Sub b()
MsgBox "b"
End Sub
Sub c()
MsgBox "c"
End Sub

--
Don Guillett
SalesAid Software

"Aaron" wrote in message
oups.com...
Hi,

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

It works well, but I cant get the third condition to work IE Call
Rockwell Both.

Perhaps it can be done a totally different way also.

I want cell f27 changing to fire a macro A

Cell k27 changing to fire a macro B

and if both cells change to fire macro C


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
Call Rockwell_Both
End If
Next myCell
End If
End Sub

Any advice greatly appreciated.

Cheers,

Aaron.

Tri state macro
 

Hi Bob,

The line when entered in place of what I had, just generates an error.

Can you elaborate any more on what I could change?

Cheers,

Aaron.

Bob Phillips wrote:
Change

If k27 < 90 And f27 < 90 Then

to

If Target("K27").Value < 90 And Target.Value < 90 Then


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Aaron" wrote in message
oups.com...
Hi,

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

It works well, but I cant get the third condition to work IE Call
Rockwell Both.

Perhaps it can be done a totally different way also.

I want cell f27 changing to fire a macro A

Cell k27 changing to fire a macro B

and if both cells change to fire macro C


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
Call Rockwell_Both
End If
Next myCell
End If
End Sub

Any advice greatly appreciated.

Cheers,

Aaron.

Tri state macro
 

Hi Don,

It is not quite working right. This is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$27" And Target < 90 Then a
If Target.Address = "$F$27" And Target < 90 And Cells(10, "a") < 90
Then
c
ElseIf Target.Address = "$K$27" And Target < 90 Then
b
End If
End Sub

By changing around the second If Target line string it just changes the
msg box popup. It is not giving me the 3 state macro trigger I need. I
also cant quite understand the And Cells(10, "a') part either.

Can you elaborate more please?

Cheers,

Aaron.


Don Guillett wrote:
maybe??? be sure you use caps for your $A instead of $a

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$10" And Target < 90 Then a
If Target.Address = "$A$11" And Target < 90 And Cells(10, "a") < 90 Then
c
ElseIf Target.Address = "$A$11" And Target < 90 Then
b
End If
End Sub

Sub a()
MsgBox "a"
End Sub
Sub b()
MsgBox "b"
End Sub
Sub c()
MsgBox "c"
End Sub

--
Don Guillett
SalesAid Software

"Aaron" wrote in message
oups.com...
Hi,

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

It works well, but I cant get the third condition to work IE Call
Rockwell Both.

Perhaps it can be done a totally different way also.

I want cell f27 changing to fire a macro A

Cell k27 changing to fire a macro B

and if both cells change to fire macro C


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
Call Rockwell_Both
End If
Next myCell
End If
End Sub

Any advice greatly appreciated.

Cheers,

Aaron.

Tri state macro
 

cells(10,"a") is the same as range("a10") so you would want cells(27,"k").
It pays to think about these things. By looking at my tested solution you
could have figured this out even if not familiar with cells.. Try it again,
without your changes.

--
Don Guillett
SalesAid Software

"Aaron" wrote in message
oups.com...
Hi Don,

It is not quite working right. This is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$27" And Target < 90 Then a
If Target.Address = "$F$27" And Target < 90 And Cells(10, "a") < 90
Then
c
ElseIf Target.Address = "$K$27" And Target < 90 Then
b
End If
End Sub

By changing around the second If Target line string it just changes the
msg box popup. It is not giving me the 3 state macro trigger I need. I
also cant quite understand the And Cells(10, "a') part either.

Can you elaborate more please?

Cheers,

Aaron.


Don Guillett wrote:
maybe??? be sure you use caps for your $A instead of $a

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$10" And Target < 90 Then a
If Target.Address = "$A$11" And Target < 90 And Cells(10, "a") < 90 Then
c
ElseIf Target.Address = "$A$11" And Target < 90 Then
b
End If
End Sub

Sub a()
MsgBox "a"
End Sub
Sub b()
MsgBox "b"
End Sub
Sub c()
MsgBox "c"
End Sub

--
Don Guillett
SalesAid Software

"Aaron" wrote in message
oups.com...
Hi,

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

It works well, but I cant get the third condition to work IE Call
Rockwell Both.

Perhaps it can be done a totally different way also.

I want cell f27 changing to fire a macro A

Cell k27 changing to fire a macro B

and if both cells change to fire macro C


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
Call Rockwell_Both
End If
Next myCell
End If
End Sub

Any advice greatly appreciated.

Cheers,

Aaron.

Tri state macro
 

Hi Don,

It works well thanks.

Cheers,

Aaron.
Don Guillett wrote:
cells(10,"a") is the same as range("a10") so you would want cells(27,"k").
It pays to think about these things. By looking at my tested solution you
could have figured this out even if not familiar with cells.. Try it again,
without your changes.

--
Don Guillett
SalesAid Software

"Aaron" wrote in message
oups.com...
Hi Don,

It is not quite working right. This is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$27" And Target < 90 Then a
If Target.Address = "$F$27" And Target < 90 And Cells(10, "a") < 90
Then
c
ElseIf Target.Address = "$K$27" And Target < 90 Then
b
End If
End Sub

By changing around the second If Target line string it just changes the
msg box popup. It is not giving me the 3 state macro trigger I need. I
also cant quite understand the And Cells(10, "a') part either.

Can you elaborate more please?

Cheers,

Aaron.


Don Guillett wrote:
maybe??? be sure you use caps for your $A instead of $a

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$10" And Target < 90 Then a
If Target.Address = "$A$11" And Target < 90 And Cells(10, "a") < 90 Then
c
ElseIf Target.Address = "$A$11" And Target < 90 Then
b
End If
End Sub

Sub a()
MsgBox "a"
End Sub
Sub b()
MsgBox "b"
End Sub
Sub c()
MsgBox "c"
End Sub

--
Don Guillett
SalesAid Software

"Aaron" wrote in message
oups.com...
Hi,

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

It works well, but I cant get the third condition to work IE Call
Rockwell Both.

Perhaps it can be done a totally different way also.

I want cell f27 changing to fire a macro A

Cell k27 changing to fire a macro B

and if both cells change to fire macro C


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
Call Rockwell_Both
End If
Next myCell
End If
End Sub

Any advice greatly appreciated.

Cheers,

Aaron.

Tri state macro
 

glad to help

--
Don Guillett
SalesAid Software

"Aaron" wrote in message
ups.com...
Hi Don,

It works well thanks.

Cheers,

Aaron.
Don Guillett wrote:
cells(10,"a") is the same as range("a10") so you would want
cells(27,"k").
It pays to think about these things. By looking at my tested solution you
could have figured this out even if not familiar with cells.. Try it
again,
without your changes.

--
Don Guillett
SalesAid Software

"Aaron" wrote in message
oups.com...
Hi Don,

It is not quite working right. This is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$27" And Target < 90 Then a
If Target.Address = "$F$27" And Target < 90 And Cells(10, "a") < 90
Then
c
ElseIf Target.Address = "$K$27" And Target < 90 Then
b
End If
End Sub

By changing around the second If Target line string it just changes the
msg box popup. It is not giving me the 3 state macro trigger I need. I
also cant quite understand the And Cells(10, "a') part either.

Can you elaborate more please?

Cheers,

Aaron.


Don Guillett wrote:
maybe??? be sure you use caps for your $A instead of $a

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$10" And Target < 90 Then a
If Target.Address = "$A$11" And Target < 90 And Cells(10, "a") < 90
Then
c
ElseIf Target.Address = "$A$11" And Target < 90 Then
b
End If
End Sub

Sub a()
MsgBox "a"
End Sub
Sub b()
MsgBox "b"
End Sub
Sub c()
MsgBox "c"
End Sub

--
Don Guillett
SalesAid Software

"Aaron" wrote in message
oups.com...
Hi,

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

It works well, but I cant get the third condition to work IE Call
Rockwell Both.

Perhaps it can be done a totally different way also.

I want cell f27 changing to fire a macro A

Cell k27 changing to fire a macro B

and if both cells change to fire macro C


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
Call Rockwell_Both
End If
Next myCell
End If
End Sub

Any advice greatly appreciated.

Cheers,

Aaron.