|
Call sub from worksheet_change
Hi all,
I've been wrestling with this and I cannot see where I have the error. For some reason it does not call the macro: "Validate". Any help would be greatly appreciated. I have the following code in Sheet 1 Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False Application.ScreenUpdating = True If Not Application.Intersect(Target, Range("F14")) Is Nothing Then With Target If .Value < "" Then Validate Else ' do other.. End If End With End If Exit Sub ws_exit: Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Error ...." End Sub ------------------------ In Module1 I have the code to validate cell B8. Sub Validate() With Range("B8").Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=A1", Formula2:="=A5" .IgnoreBlank = True .InCellDropdown = True End With End Sub ---------------------- Thanks in advance for any help you can give me. |
Call sub from worksheet_change
You need:
Application.EnableEvents = True Application.ScreenUpdating = True before exiting ( you are currently executing them only on error) -- Gary''s Student - gsnu200783 "JMJ" wrote: Hi all, I've been wrestling with this and I cannot see where I have the error. For some reason it does not call the macro: "Validate". Any help would be greatly appreciated. I have the following code in Sheet 1 Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False Application.ScreenUpdating = True If Not Application.Intersect(Target, Range("F14")) Is Nothing Then With Target If .Value < "" Then Validate Else ' do other.. End If End With End If Exit Sub ws_exit: Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Error ...." End Sub ------------------------ In Module1 I have the code to validate cell B8. Sub Validate() With Range("B8").Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=A1", Formula2:="=A5" .IgnoreBlank = True .InCellDropdown = True End With End Sub ---------------------- Thanks in advance for any help you can give me. |
Call sub from worksheet_change
It still doesn't call it. ...
"Gary''s Student" wrote: You need: Application.EnableEvents = True Application.ScreenUpdating = True before exiting ( you are currently executing them only on error) -- Gary''s Student - gsnu200783 "JMJ" wrote: Hi all, I've been wrestling with this and I cannot see where I have the error. For some reason it does not call the macro: "Validate". Any help would be greatly appreciated. I have the following code in Sheet 1 Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False Application.ScreenUpdating = True If Not Application.Intersect(Target, Range("F14")) Is Nothing Then With Target If .Value < "" Then Validate Else ' do other.. End If End With End If Exit Sub ws_exit: Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Error ...." End Sub ------------------------ In Module1 I have the code to validate cell B8. Sub Validate() With Range("B8").Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=A1", Formula2:="=A5" .IgnoreBlank = True .InCellDropdown = True End With End Sub ---------------------- Thanks in advance for any help you can give me. |
Call sub from worksheet_change
First fix the event code
Next run something like: Sub en() Application.EnableEvents = True End Sub Then try the data entry -- Gary''s Student - gsnu200783 "JMJ" wrote: It still doesn't call it. ... "Gary''s Student" wrote: You need: Application.EnableEvents = True Application.ScreenUpdating = True before exiting ( you are currently executing them only on error) -- Gary''s Student - gsnu200783 "JMJ" wrote: Hi all, I've been wrestling with this and I cannot see where I have the error. For some reason it does not call the macro: "Validate". Any help would be greatly appreciated. I have the following code in Sheet 1 Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False Application.ScreenUpdating = True If Not Application.Intersect(Target, Range("F14")) Is Nothing Then With Target If .Value < "" Then Validate Else ' do other.. End If End With End If Exit Sub ws_exit: Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Error ...." End Sub ------------------------ In Module1 I have the code to validate cell B8. Sub Validate() With Range("B8").Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=A1", Formula2:="=A5" .IgnoreBlank = True .InCellDropdown = True End With End Sub ---------------------- Thanks in advance for any help you can give me. |
Call sub from worksheet_change
The events are enabled. "Gary''s Student" wrote: First fix the event code Next run something like: Sub en() Application.EnableEvents = True End Sub Then try the data entry -- Gary''s Student - gsnu200783 "JMJ" wrote: It still doesn't call it. ... "Gary''s Student" wrote: You need: Application.EnableEvents = True Application.ScreenUpdating = True before exiting ( you are currently executing them only on error) -- Gary''s Student - gsnu200783 "JMJ" wrote: Hi all, I've been wrestling with this and I cannot see where I have the error. For some reason it does not call the macro: "Validate". Any help would be greatly appreciated. I have the following code in Sheet 1 Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False Application.ScreenUpdating = True If Not Application.Intersect(Target, Range("F14")) Is Nothing Then With Target If .Value < "" Then Validate Else ' do other.. End If End With End If Exit Sub ws_exit: Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Error ...." End Sub ------------------------ In Module1 I have the code to validate cell B8. Sub Validate() With Range("B8").Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=A1", Formula2:="=A5" .IgnoreBlank = True .InCellDropdown = True End With End Sub ---------------------- Thanks in advance for any help you can give me. |
Call sub from worksheet_change
This works for me:
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False Application.ScreenUpdating = True If Not Intersect(Target, Range("F14")) Is Nothing Then With Target If .Value < "" Then Call Validate Else End If End With End If Application.EnableEvents = True Application.ScreenUpdating = True Exit Sub ws_exit: Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Error ...." End Sub Note I changed about three lines. -- Gary''s Student - gsnu200783 "JMJ" wrote: The events are enabled. "Gary''s Student" wrote: First fix the event code Next run something like: Sub en() Application.EnableEvents = True End Sub Then try the data entry -- Gary''s Student - gsnu200783 "JMJ" wrote: It still doesn't call it. ... "Gary''s Student" wrote: You need: Application.EnableEvents = True Application.ScreenUpdating = True before exiting ( you are currently executing them only on error) -- Gary''s Student - gsnu200783 "JMJ" wrote: Hi all, I've been wrestling with this and I cannot see where I have the error. For some reason it does not call the macro: "Validate". Any help would be greatly appreciated. I have the following code in Sheet 1 Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False Application.ScreenUpdating = True If Not Application.Intersect(Target, Range("F14")) Is Nothing Then With Target If .Value < "" Then Validate Else ' do other.. End If End With End If Exit Sub ws_exit: Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Error ...." End Sub ------------------------ In Module1 I have the code to validate cell B8. Sub Validate() With Range("B8").Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=A1", Formula2:="=A5" .IgnoreBlank = True .InCellDropdown = True End With End Sub ---------------------- Thanks in advance for any help you can give me. |
Call sub from worksheet_change
Thanks!
For some reason it did not like the "Application.Intersect" (?) ... Correction: At the beginning of the sub I have an Application.ScreenUpdating = True and it should be an "Application.ScreenUpdating = False" "Gary''s Student" wrote: This works for me: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False Application.ScreenUpdating = True If Not Intersect(Target, Range("F14")) Is Nothing Then With Target If .Value < "" Then Call Validate Else End If End With End If Application.EnableEvents = True Application.ScreenUpdating = True Exit Sub ws_exit: Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Error ...." End Sub Note I changed about three lines. -- Gary''s Student - gsnu200783 "JMJ" wrote: The events are enabled. "Gary''s Student" wrote: First fix the event code Next run something like: Sub en() Application.EnableEvents = True End Sub Then try the data entry -- Gary''s Student - gsnu200783 "JMJ" wrote: It still doesn't call it. ... "Gary''s Student" wrote: You need: Application.EnableEvents = True Application.ScreenUpdating = True before exiting ( you are currently executing them only on error) -- Gary''s Student - gsnu200783 "JMJ" wrote: Hi all, I've been wrestling with this and I cannot see where I have the error. For some reason it does not call the macro: "Validate". Any help would be greatly appreciated. I have the following code in Sheet 1 Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False Application.ScreenUpdating = True If Not Application.Intersect(Target, Range("F14")) Is Nothing Then With Target If .Value < "" Then Validate Else ' do other.. End If End With End If Exit Sub ws_exit: Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Error ...." End Sub ------------------------ In Module1 I have the code to validate cell B8. Sub Validate() With Range("B8").Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=A1", Formula2:="=A5" .IgnoreBlank = True .InCellDropdown = True End With End Sub ---------------------- Thanks in advance for any help you can give me. |
| All times are GMT +1. The time now is 07:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com