Sorry for the dated advise.

I confirmed today that the Worksheet_Change event IS fired
when you change a cell value by means of selection from a
dropdown list for xl2000 and xl2002. This is not the case
for xl97. I'm running xl97. See Dave Peterson's response
to my post "xl97 and Worksheet_Change event?" made today
Sept. 9 at 7:36 PM.

Regards,
Greg

-----Original Message-----
I don't believe that the Worksheet_Change event is fired
when you change a cell by means of a selection from a
dropdown list, whether Data Validation, Listbox or
whatever. There is a flaw in your code but I think it's
academic because it still won't work.

Suggested is that you use a ListBox from the Control
toolbox use the Click event of the Listbox to do what you
want. The code would be quite similar to what you have.

Just my humble opinion.

Regards,
Greg


-----Original Message-----
example: (refer help5.xls)

when I select "ctmr" from the validation list, excel
does NOT autofill
"attn" and "tel". what's wrong with the sub procedures?

if the hyperlink does not work,
TYPE(NOT CLICK) this url DIRECTLY IN THE BROWSER :
http://www.geocities.com/remember_it/help5.xls

--------------------------------------------------

Option Explicit

Dim rNo As Integer, cNo As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

rNo = Target.Row
cNo = Target.Column

If rNo = 3 And cNo = 6 Then
fndCtmr
End If

End Sub

Private Sub fndCtmr()

Dim rCtmr As Integer

Application.EnableEvents = False

Cells(5, 6) = "" 'cls present ctmr info
Cells(7, 6) = ""

rCtmr = 2 'start of ctmr record

Do Until Cells(rCtmr, 1) = Empty 'do until the end
of ctmr
list

If Cells(rCtmr, 1) = Cells(3, 6) Then 'if fnd ctmr
Exit Do
End If

rCtmr = rCtmr + 1 'chk next record in db
Loop

Cells(5, 6) = Cells(rCtmr, 2) 'fill attn
Cells(7, 6) = Cells(rCtmr, 3) 'fill tel


Application.EnableEvents = True

End Sub
.

.