Excel event code with data validation

Question

QUESTION: I have a cell that is populated by a Data Validation List of States in Excel 2003. When a value is selected with this drop-down, I want to populate another cell with the value. I tried the following worksheet change event code, but the code isn't recognizing the cell change if it is done through data validation. Any way around this?

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Application.Intersect(Target, Range("Office")) Is Nothing Then
       If Len(Range("Office")) <> "" Then
           Worksheets("Key").Range("OfficeSelect").Value = Range("Office").Value
       Else
           Worksheets("Key").Range("OfficeSelect").Value = ""
       End If
   End If

End Sub

ANSWER: Whatversion are you using? If I recall correctly this was a bug in I think2000?

---------- FOLLOW-UP ----------

QUESTION: I'm using2003. I was just wondering if there's a workaround for this. My company is upgrading toin November.

ANSWER: It works fine on my2003, so I don't understand why it would not work on yours. Are you sure macro's are enabled on the file?

---------- FOLLOW-UP ----------

QUESTION: Yes - macros are enabled. The range called Office has the data validation (drop-down list with states). When I change the state on the in-cell drop-down, I would expect the code to recognize that the target has changed and run the code. I even put a simple msgbox in the code to test whether or not it's running and that doesn't even pop-up.

Answer

Not sure what causes your problem. I do see something wrong with your code though:

If Len(Range("Office")) <> "" Then

Len(Range("Office")) returns the length of the value of the range "Office" and you are checking if that is larger then an empty string. If the cell Office is empty, this test returns true, not false!

I'd use:

If Len(Range("Office")) > 0 Then

Hunch: are you sure the validated cell you are testing is part of the named range "Office"?