| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| replace state names with state code abbreviations | Excel Worksheet Functions | |||
| How can I show state-by-state data (as silos) on a map of NA | Charts and Charting in Excel | |||
| City State Zip | Excel Worksheet Functions | |||
| State Map | Excel Discussion (Misc queries) | |||
| Converting State Names to State Abbreviations | Excel Discussion (Misc queries) | |||
Linear Mode
