| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents on change
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear contents on change
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 -- Gary''s Student - gsnu200832 "Suzanne" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear contents on change
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,HAZCO DE,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,HAZNAME 2,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" wrote: 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 -- Gary''s Student - gsnu200832 "Suzanne" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear contents on change
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. -- Gary''s Student - gsnu200832 "Suzanne" wrote: 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,HAZCO DE,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,HAZNAME 2,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" wrote: 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 -- Gary''s Student - gsnu200832 "Suzanne" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Clear Contents | Excel Discussion (Misc queries) | |||
| clear contents | Excel Programming | |||
| Macro to clear range contents when cell contents are changed by us | Excel Programming | |||
| Clear contents | Excel Programming | |||
| Clear Contents Help | Excel Programming | |||
Hybrid Mode
