|
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. |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 07:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com