what's wrong with this sub procedures?

  • Thread starter Thread starter active_x
  • Start date Start date
A

active_x

example: (refer help5.xls)
when I select "ctmr" from the validation list, excel does NOT autofill
Click to expand...
"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 :
Click to expand...
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
 
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
 
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
 
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
 
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 said:
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

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
Click to expand...
Click to expand...
 
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.



Don Guillett said:
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 said:
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

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


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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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.



David,

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

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

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


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
Click to expand...
Click to expand...
Click to expand...
 
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
 
Greg - your advice is still accurate for XL98/01/v.X for Mac, so
cross-platform applications still can't use validation dropdown to
fire a Worksheet_Change.
 
Back
Top