| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with action on range change
Greetings:
Im having difficulty getting a program to execute on a cell change (F31). Cell F31 is actually a 1R x 5C range of cells (F31-J31 merged) with a validation list. There are three possibilities in the list. The text for each possibility is long, but for simplicity, assume that I need all five cells and the values in the list are X, Y and Z. When the user selects a value from the list in F31, cells F59 and F67 should change. If the user chooses X in F31, I want F59 to show Yes and F67 to show No. If the user chooses Y in F31, I want F59 to show No and F67 to show Yes. If the user chooses Z in F31, I want F59 to show Yes and F67 to show Yes. If the user deletes the entry in F31, I want both F59 and F67 to go blank. The first three Ifs above work fine. However, when I delete an entry in F31, F59 and F67 do not change. Here is the relevant part of the code: If Target.Address = "$F$31" Then If MM.Range("F31") = "X" Then MM.Cells(59, 6) = "Yes" MM.Cells(67, 6) = "No" ElseIf MM.Range("F31") = "Y" Then MM.Cells(59, 6) = "No" MM.Cells(67, 6) = "Yes" ElseIf MM.Range("F31") = "Z" Then MM.Cells(59, 6) = "Yes" MM.Cells(67, 6) = "Yes" Else MM.Cells(59, 6) = "" MM.Cells(67, 6) = "" End If End If I notice that it DOES work if I unmerge the cells (and use ONLY F31 for real), but I really do need the five-cell range. Any ideas as to how I can get this to work? Help is appreciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with action on range change
Maybe...
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.MergeArea.Cells(1).Address = "$F$31" Then If Me.Range("F31") = "X" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "No" ElseIf Me.Range("F31") = "Y" Then Me.Cells(59, 6) = "No" Me.Cells(67, 6) = "Yes" ElseIf Me.Range("F31") = "Z" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "Yes" Else Me.Cells(59, 6) = "" Me.Cells(67, 6) = "" End If End If End Sub ======= If you're sharing the workbook with people using xl97, you may want to read Debra Dalgleish's notes: http://contextures.com/xlDataVal08.html#Change Marty wrote: Greetings: Im having difficulty getting a program to execute on a cell change (F31). Cell F31 is actually a 1R x 5C range of cells (F31-J31 merged) with a validation list. There are three possibilities in the list. The text for each possibility is long, but for simplicity, assume that I need all five cells and the values in the list are X, Y and Z. When the user selects a value from the list in F31, cells F59 and F67 should change. If the user chooses X in F31, I want F59 to show Yes and F67 to show No. If the user chooses Y in F31, I want F59 to show No and F67 to show Yes. If the user chooses Z in F31, I want F59 to show Yes and F67 to show Yes. If the user deletes the entry in F31, I want both F59 and F67 to go blank. The first three Ifs above work fine. However, when I delete an entry in F31, F59 and F67 do not change. Here is the relevant part of the code: If Target.Address = "$F$31" Then If MM.Range("F31") = "X" Then MM.Cells(59, 6) = "Yes" MM.Cells(67, 6) = "No" ElseIf MM.Range("F31") = "Y" Then MM.Cells(59, 6) = "No" MM.Cells(67, 6) = "Yes" ElseIf MM.Range("F31") = "Z" Then MM.Cells(59, 6) = "Yes" MM.Cells(67, 6) = "Yes" Else MM.Cells(59, 6) = "" MM.Cells(67, 6) = "" End If End If I notice that it DOES work if I unmerge the cells (and use ONLY F31 for real), but I really do need the five-cell range. Any ideas as to how I can get this to work? Help is appreciated. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with action on range change
Thanks for the response, Dave. Nobody will be using this with xl97, but
thanks for the tip. Basically, you changed my: If Target.Address = "$F$31" Then to: If Target.MergeArea.Cells(1).Address = "$F$31" Then. As before, that works when I make a text selection in F31, but now when I delete it gives me an "Application-defined or object-defined" error. Any other ideas? "Dave Peterson" wrote: Maybe... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.MergeArea.Cells(1).Address = "$F$31" Then If Me.Range("F31") = "X" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "No" ElseIf Me.Range("F31") = "Y" Then Me.Cells(59, 6) = "No" Me.Cells(67, 6) = "Yes" ElseIf Me.Range("F31") = "Z" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "Yes" Else Me.Cells(59, 6) = "" Me.Cells(67, 6) = "" End If End If End Sub ======= If you're sharing the workbook with people using xl97, you may want to read Debra Dalgleish's notes: http://contextures.com/xlDataVal08.html#Change Marty wrote: Greetings: Iâm having difficulty getting a program to execute on a cell change (F31). Cell F31 is actually a 1R x 5C range of cells (F31-J31 merged) with a validation list. There are three possibilities in the list. The text for each possibility is long, but for simplicity, assume that I need all five cells and the values in the list are X, Y and Z. When the user selects a value from the list in F31, cells F59 and F67 should change. If the user chooses X in F31, I want F59 to show âœYesâ and F67 to show âœNoâ. If the user chooses Y in F31, I want F59 to show âœNoâ and F67 to show âœYesâ. If the user chooses Z in F31, I want F59 to show âœYesâ and F67 to show âœYesâ. If the user deletes the entry in F31, I want both F59 and F67 to go blank. The first three âœIfsâ above work fine. However, when I delete an entry in F31, F59 and F67 do not change. Here is the relevant part of the code: If Target.Address = "$F$31" Then If MM.Range("F31") = "X" Then MM.Cells(59, 6) = "Yes" MM.Cells(67, 6) = "No" ElseIf MM.Range("F31") = "Y" Then MM.Cells(59, 6) = "No" MM.Cells(67, 6) = "Yes" ElseIf MM.Range("F31") = "Z" Then MM.Cells(59, 6) = "Yes" MM.Cells(67, 6) = "Yes" Else MM.Cells(59, 6) = "" MM.Cells(67, 6) = "" End If End If I notice that it DOES work if I unmerge the cells (and use ONLY F31 for real), but I really do need the five-cell range. Any ideas as to how I can get this to work? Help is appreciated. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with action on range change
Poor testing on my part--I only checked X, Y, Z.
How about: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells(1).Address = "$F$31" Then Application.EnableEvents = False If Me.Range("F31") = "X" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "No" ElseIf Me.Range("F31") = "Y" Then Me.Cells(59, 6) = "No" Me.Cells(67, 6) = "Yes" ElseIf Me.Range("F31") = "Z" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "Yes" Else Me.Cells(59, 6) = "" Me.Cells(67, 6) = "" End If Application.EnableEvents = True End If End Sub But I think I'd use: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) With Target.Cells(1) If .Address = "$F$31" Then Application.EnableEvents = False If .Value = "X" Then Me.Cells(59, 6).Value = "Yes" Me.Cells(67, 6).Value = "No" ElseIf .Value = "Y" Then Me.Cells(59, 6).Value = "No" Me.Cells(67, 6).Value = "Yes" ElseIf .Value = "Z" Then Me.Cells(59, 6).Value = "Yes" Me.Cells(67, 6).Value = "Yes" Else Me.Cells(59, 6).Value = "" Me.Cells(67, 6).Value = "" End If Application.EnableEvents = True End If End With End Sub Marty wrote: Thanks for the response, Dave. Nobody will be using this with xl97, but thanks for the tip. Basically, you changed my: If Target.Address = "$F$31" Then to: If Target.MergeArea.Cells(1).Address = "$F$31" Then. As before, that works when I make a text selection in F31, but now when I delete it gives me an "Application-defined or object-defined" error. Any other ideas? "Dave Peterson" wrote: Maybe... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.MergeArea.Cells(1).Address = "$F$31" Then If Me.Range("F31") = "X" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "No" ElseIf Me.Range("F31") = "Y" Then Me.Cells(59, 6) = "No" Me.Cells(67, 6) = "Yes" ElseIf Me.Range("F31") = "Z" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "Yes" Else Me.Cells(59, 6) = "" Me.Cells(67, 6) = "" End If End If End Sub ======= If you're sharing the workbook with people using xl97, you may want to read Debra Dalgleish's notes: http://contextures.com/xlDataVal08.html#Change Marty wrote: Greetings: Iâm having difficulty getting a program to execute on a cell change (F31). Cell F31 is actually a 1R x 5C range of cells (F31-J31 merged) with a validation list. There are three possibilities in the list. The text for each possibility is long, but for simplicity, assume that I need all five cells and the values in the list are X, Y and Z. When the user selects a value from the list in F31, cells F59 and F67 should change. If the user chooses X in F31, I want F59 to show âœYesâ and F67 to show âœNoâ. If the user chooses Y in F31, I want F59 to show âœNoâ and F67 to show âœYesâ. If the user chooses Z in F31, I want F59 to show âœYesâ and F67 to show âœYesâ. If the user deletes the entry in F31, I want both F59 and F67 to go blank. The first three âœIfsâ above work fine. However, when I delete an entry in F31, F59 and F67 do not change. Here is the relevant part of the code: If Target.Address = "$F$31" Then If MM.Range("F31") = "X" Then MM.Cells(59, 6) = "Yes" MM.Cells(67, 6) = "No" ElseIf MM.Range("F31") = "Y" Then MM.Cells(59, 6) = "No" MM.Cells(67, 6) = "Yes" ElseIf MM.Range("F31") = "Z" Then MM.Cells(59, 6) = "Yes" MM.Cells(67, 6) = "Yes" Else MM.Cells(59, 6) = "" MM.Cells(67, 6) = "" End If End If I notice that it DOES work if I unmerge the cells (and use ONLY F31 for real), but I really do need the five-cell range. Any ideas as to how I can get this to work? Help is appreciated. -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with action on range change
Excellent! Thanks very much.
"Dave Peterson" wrote: Poor testing on my part--I only checked X, Y, Z. How about: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells(1).Address = "$F$31" Then Application.EnableEvents = False If Me.Range("F31") = "X" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "No" ElseIf Me.Range("F31") = "Y" Then Me.Cells(59, 6) = "No" Me.Cells(67, 6) = "Yes" ElseIf Me.Range("F31") = "Z" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "Yes" Else Me.Cells(59, 6) = "" Me.Cells(67, 6) = "" End If Application.EnableEvents = True End If End Sub But I think I'd use: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) With Target.Cells(1) If .Address = "$F$31" Then Application.EnableEvents = False If .Value = "X" Then Me.Cells(59, 6).Value = "Yes" Me.Cells(67, 6).Value = "No" ElseIf .Value = "Y" Then Me.Cells(59, 6).Value = "No" Me.Cells(67, 6).Value = "Yes" ElseIf .Value = "Z" Then Me.Cells(59, 6).Value = "Yes" Me.Cells(67, 6).Value = "Yes" Else Me.Cells(59, 6).Value = "" Me.Cells(67, 6).Value = "" End If Application.EnableEvents = True End If End With End Sub Marty wrote: Thanks for the response, Dave. Nobody will be using this with xl97, but thanks for the tip. Basically, you changed my: If Target.Address = "$F$31" Then to: If Target.MergeArea.Cells(1).Address = "$F$31" Then. As before, that works when I make a text selection in F31, but now when I delete it gives me an "Application-defined or object-defined" error. Any other ideas? "Dave Peterson" wrote: Maybe... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.MergeArea.Cells(1).Address = "$F$31" Then If Me.Range("F31") = "X" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "No" ElseIf Me.Range("F31") = "Y" Then Me.Cells(59, 6) = "No" Me.Cells(67, 6) = "Yes" ElseIf Me.Range("F31") = "Z" Then Me.Cells(59, 6) = "Yes" Me.Cells(67, 6) = "Yes" Else Me.Cells(59, 6) = "" Me.Cells(67, 6) = "" End If End If End Sub ======= If you're sharing the workbook with people using xl97, you may want to read Debra Dalgleish's notes: http://contextures.com/xlDataVal08.html#Change Marty wrote: Greetings: Iââ¬â¢m having difficulty getting a program to execute on a cell change (F31). Cell F31 is actually a 1R x 5C range of cells (F31-J31 merged) with a validation list. There are three possibilities in the list. The text for each possibility is long, but for simplicity, assume that I need all five cells and the values in the list are X, Y and Z. When the user selects a value from the list in F31, cells F59 and F67 should change. If the user chooses X in F31, I want F59 to show ââ¬ÅYesââ¬Â and F67 to show ââ¬ÅNoââ¬Â. If the user chooses Y in F31, I want F59 to show ââ¬ÅNoââ¬Â and F67 to show ââ¬ÅYesââ¬Â. If the user chooses Z in F31, I want F59 to show ââ¬ÅYesââ¬Â and F67 to show ââ¬ÅYesââ¬Â. If the user deletes the entry in F31, I want both F59 and F67 to go blank. The first three ââ¬ÅIfsââ¬Â above work fine. However, when I delete an entry in F31, F59 and F67 do not change. Here is the relevant part of the code: If Target.Address = "$F$31" Then If MM.Range("F31") = "X" Then MM.Cells(59, 6) = "Yes" MM.Cells(67, 6) = "No" ElseIf MM.Range("F31") = "Y" Then MM.Cells(59, 6) = "No" MM.Cells(67, 6) = "Yes" ElseIf MM.Range("F31") = "Z" Then MM.Cells(59, 6) = "Yes" MM.Cells(67, 6) = "Yes" Else MM.Cells(59, 6) = "" MM.Cells(67, 6) = "" End If End If I notice that it DOES work if I unmerge the cells (and use ONLY F31 for real), but I really do need the five-cell range. Any ideas as to how I can get this to work? Help is appreciated. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change to a Cell causes action | Excel Programming | |||
| Change in action when converting to *.xla | Excel Programming | |||
| Cell change causes action | Excel Programming | |||
| Evaluate cells for change and then action | Excel Programming | |||
| Automating a date field to change on certain action | Excel Programming | |||
Linear Mode
