|
what's wrong with this sub procedures?
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 |
what's wrong with this sub procedures?
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 . |
what's wrong with this sub procedures?
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 |
what's wrong with this sub procedures?
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 |
what's wrong with this sub procedures?
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 |
what's wrong with this sub procedures?
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 |
what's wrong with this sub procedures?
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 |
what's wrong with this sub procedures?
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 . . |
what's wrong with this sub procedures?
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. In article , "Greg Wilson" wrote: 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. |
| All times are GMT +1. The time now is 03:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com