Dependent Cell - Change Value Problem

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top