The best way do to this was posted by Dave Hawley - use error handling:

Private Sub Worksheet_Change(ByVal Target As Range)
Static cnt As Long
On Error goto ErrHandler
cnt = cnt + 1
Application.StatusBar = cnt
If Target.Row = 3 And Target.Column = 6 Then
x = Range("a" & Rows.Count).End(xlUp).Row
Application.EnableEvents = False
Range("a1:c" & x).FillDown
End If
Errhandler:
Application.EnableEvents = True
End Sub

Note that you always pass through the error handler (even when there is no
error, but even more importantly when there is an error as well) and events
are enabled.

don't do the normal

Exit Sub
ErrHandler:
' handler code
End Sub

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
Well, If Tom sez to, gotta do.
I will put this in my personal.xls and assign a custom button immediately.
But, for many users I'll bet the enable events might trigger a lot of
"why doesn't my macro work anymore?"
Whenever I do include it, I also include the fixevents macro just below.

sub fixevents
Application.EnableEvents = True
end sub

"David McRitchie" wrote in message
...
Hi Don,

cuz Tom sez to.

Because the change event is changing other cells and
you know what you want to change them to, you don't want
to be running it recursively. Not noticeable on small
test data, or data when not much will be changed, or the
change is to a column outside of the area of interest, but
it could take a big hit with lots of data.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Don Guillett" wrote in message
...
David,

That's why I avoid enable events unless absolutely necessary.
Why do you think advisable here? I tested and it seemed to work.

"David McRitchie" wrote in message
...
Hi ...
Note that Don's code should run faster because there are
no loops.

Don meant fill down to the last *used* cell in column A.
I think that Events should be disabled while the macro
is making changes, which you included in your
event macro but Don failed to include.

Application.EnableEvents = False
and must be turned back on before exiting
Application.EnableEvents = True

see http://www.mvps.org/dmcritchie/excel/event.htm#change

Failure to reenable events will result in Event macros doing
nothing, and it is inevitable that you will have a macro terminate
when you have events turned off. After fixing your macro you
will have to run a regular macro with the code
Application.EnableEvents = True
see http://www.mvps.org/dmcritchie/excel/event.htm#problems

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Don Guillett" wrote in message
...
A worksheet_change event can fire a macro such as

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 6 Then fndctmr
End Sub

But if you are trying to fill down a1,b1,c1 to the last row of col
A
then
this will do.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 6 Then
x = Range("a" & Rows.Count).End(xlUp).Row
Range("a1:c" & x).FillDown
End If
End Sub

HTH


"active_x" wrote in 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