Problem with action on range change

[Marty]

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]

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]

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]

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]

Excellent! Thanks very much.

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