Clear contents on change

[Suzanne]

If a cell in COL O changes, I want to clear the contents of COL P, Q R (in
the same row)
If a cell in COL Q changes, I want to clear the contents of COL O, P, R
(again in the same row)

I think I had the following working in Excel 2003, but its not working in
2007.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

With Target
Select Case .Column
Case 15 'column O
Application.EnableEvents = False
Cells(.Row, "P").ClearContents
Cells(.Row, "Q").ClearContents
Cells(.Row, "R").ClearContents
Application.EnableEvents = True
Case 17 'column Q
Application.EnableEvents = False
Cells(.Row, "O").ClearContents
Cells(.Row, "P").ClearContents
Cells(.Row, "R").ClearContents
Application.EnableEvents = True
End Select
End With

End Sub

 

[Gary''s Student]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set rr = Union(Range("O:O"), Range("Q:Q"))
If Intersect(t, rr) Is Nothing Then Exit Sub
c = t.Column
r = t.Row
Application.EnableEvents = False
If c = 15 Then
Cells(r, "P").ClearContents
Cells(r, "Q").ClearContents
Cells(r, "R").ClearContents
Else
Cells(r, "P").ClearContents
Cells(r, "O").ClearContents
Cells(r, "R").ClearContents
End If
Application.EnableEvents = True
End Sub

 

[Suzanne]

Didn't work. Details I didn't include in the original message (if it helps):

Option Explicit

COL O is a dropdown listing hazard codes
=IF(P2="",HAZCODELIST,INDEX(HAZCODE,MATCH(P2,HAZCODE,0)))
COL P is a dropdown listing the name of the hazard -- based on COL O
selection
=OFFSET(HAZCODE,MATCH(O2,HAZCODE,0)-1,1,COUNTIF(HAZCODE,O2),1)

COL Q is a dropdown listing hazard names (to search by name instead of code)
=IF(R2="",HAZNAME2,INDEX(HAZNAME2,MATCH(R2,HAZNAME2,0)))
COL R is a dropdown of hazard codes -- based on COL Q selection
=OFFSET(HAZNAME2,MATCH(Q2,HAZNAME2,0)-1,1,COUNTIF(HAZNAME2,Q2),1)

If users select COL O, COL P populates with names associated with the
selection (and so on with COL Q/R)

What I need to do: Eliminate the opportunity for population of both COL O/P
and COL Q/R (it MUST be one or the other)

Finally, this wasn't possible in Excel 2003 -- can it be done in 2007??

Clear COL P if COL O changes (same for COL Q/R)

For example, if the cell in COL P is filled, users must manually clear
(delete) the cell content if they want to change the selection from COL O

The kicker... I don't want COL P to clear unless COL O actually
changes (i.e., don't clear on focus; do clear on change)

Thanks -- Suzanne

 

[Gary''s Student]

I see why neither your nor my code works. If the cells in columns O & Q have
formulas, the change event won't get triggered, we would need some kind of
Calculate event.

 

[Suzanne]

Debug is stopping at Set t = Target (variable not defined)

I'm not good enough at this to figure out the fix (I did make a stab at it).

Suzanne

 

[Gary''s Student]

This is a symptom of the general problem. There is no Target! the Calculate
event does not have one. Excel does not keep track of which formulas
produced a new result and which just re-calculated the previous value.

To make this work, we would to "remember" the values of all the cells in
columns O & Q and then compare our "memory" to the actual values for each at
each event.

That is why this is so difficult.

Sorry