M
max
I have a Change Event procedure which will clear the active cell's
contents in the "INGREDIENTS" range if the "CATEGORY" target cell's
value is changed.
My problem occurs if the user should choose the same list value in the
active cell "CATEGORY" dropdown List
..
The code still clears the contents of the offset cell.
I would like a way to prevent this from happening.
On a "Recipe Sheet", I have two range columns: "CATEGORY" and
"INGREDIENTS",
which refer to the "CategoryColumn" and "IngredientsColumn" ranges on
the "IngredientsLists" Sheet.
The Validation list formula for the "CATEGORY" cells
"=CATEGORY_NAMES" which is the reference list of categories.
The Validation list formula for the "INGREDIENTS" cells
"=OFFSET(CategoryStart,MATCH($B1,CategoryColumn,0),1,COUNTIF
(CategoryColumn,$B1),1)"
========================================
CODE:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Range("CATEGORY") Is Nothing Then Exit Sub
On Error GoTo 0
If Not Intersect(Target, Range("CATEGORY")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1) = ""
Application.EnableEvents = True
End If
End Sub
Thanks Max
contents in the "INGREDIENTS" range if the "CATEGORY" target cell's
value is changed.
My problem occurs if the user should choose the same list value in the
active cell "CATEGORY" dropdown List
..
The code still clears the contents of the offset cell.
I would like a way to prevent this from happening.
On a "Recipe Sheet", I have two range columns: "CATEGORY" and
"INGREDIENTS",
which refer to the "CategoryColumn" and "IngredientsColumn" ranges on
the "IngredientsLists" Sheet.
The Validation list formula for the "CATEGORY" cells
"=CATEGORY_NAMES" which is the reference list of categories.
The Validation list formula for the "INGREDIENTS" cells
"=OFFSET(CategoryStart,MATCH($B1,CategoryColumn,0),1,COUNTIF
(CategoryColumn,$B1),1)"
========================================
CODE:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Range("CATEGORY") Is Nothing Then Exit Sub
On Error GoTo 0
If Not Intersect(Target, Range("CATEGORY")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1) = ""
Application.EnableEvents = True
End If
End Sub
Thanks Max