Hi there

I have a spreadsheet in which one column has data validation set. So the cells in this column have drop-down lists with the pre-set allowed values. Now I want the cell in the neighboring column to be set to some default value when the user selects an item from the drop-down list.

This seemed like it would be easy, using the Worksheet_Change event handler. When a cell in the first column is changed, it initiates a function which sets the value in the neighboring column to some default value:
Target.Offset(0,1).Value = "default".

The problem is that it doesn't work when using the drop-down list. If the user types in the value manually, or if a cell in the first column is deleted, it DOES fill in the neighboring column. But when using the drop-down list, it doesn't work.

The problem is NOT that Worksheet_Change doesn't recognize the change. I saw in some earlier articles that pre-Excel-97, changes made through drop-down lists are not recognized by the WorkSheet_change function. Well, I'm using Excel-2000, and the change IS recognized (which I know because the MsgBox pops up), but the value in the neighboring cell is simply not changed.

Here is my function:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim VRange As Range
Set VRange = Range("changeCAS")
If Not Intersect(Target, VRange) Is Nothing Then
MsgBox ("change recognized!!")
If MsgBox("Fill in default CAS parameters for this row?", vbYesNo) = vbYes Then
Target.Offset(0, 1).Value = "this is the default"
End If
End If
End Sub

Any input would be greatly appreciated!!!
Thanks in advance.
Emma