Loop with Cell Change Event
 

Hello,


I'm new at using VBA and I'm trying to enhance the input of an excell
sheet on an automated basis, but I run into some following problem.
Checking the input and should be triggert after the user does
datainput, and this on an automated basis. So I was using a
Worksheet_Change event. But since the Checking procedure might
overwrite (certain cells of my input) I get into a loop, cause of a new
Cell Change event.

I Tried to solve this by using a boolean that is true when checking and
after check is set on No again, but it doesn't seem to work. (By
following this boolean I have seen that the program kind of looses the
value when the Worksheet_Change event is (re)started.

My Code looks like this :

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

Dim Processing As Boolean

Private Sub Workbook_Open()

Processing = False

End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)

If Processing = False Then
Processing = True

'Sub that might or might not change the content of any cell on
this sheet
Module1.Cellverification

Processing = False
End If
End Sub


________________________________________________

For simple data entry, the extra loop isn't a problem, but once if the
user starts copying larger cell ranges into the sheet I have a problem.

Is it possible to keep this value of my boolean? Or to ommit changes
that are made by the
procedure I run? Or there an event that is by actions of the user
interface (but after data entry)

Thanks for your input,



Adrian

Loop with Cell Change Event
 

If your change event is going to change anything then you want to disable
events while the code is running.

Private Sub Worksheet_Change(ByVal Target As Range)
on error goto Errorhandler

application.eneableevents = false
'Sub that might or might not change the content of any cell on
Module1.Cellverification
Errorhandler:
application.enableevents = true
End Sub


--
HTH...

Jim Thomlinson


"Adrian" wrote:

Hello,


I'm new at using VBA and I'm trying to enhance the input of an excell
sheet on an automated basis, but I run into some following problem.
Checking the input and should be triggert after the user does
datainput, and this on an automated basis. So I was using a
Worksheet_Change event. But since the Checking procedure might
overwrite (certain cells of my input) I get into a loop, cause of a new
Cell Change event.

I Tried to solve this by using a boolean that is true when checking and
after check is set on No again, but it doesn't seem to work. (By
following this boolean I have seen that the program kind of looses the
value when the Worksheet_Change event is (re)started.

My Code looks like this :

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

Dim Processing As Boolean

Private Sub Workbook_Open()

Processing = False

End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)

If Processing = False Then
Processing = True

'Sub that might or might not change the content of any cell on
this sheet
Module1.Cellverification

Processing = False
End If
End Sub


________________________________________________

For simple data entry, the extra loop isn't a problem, but once if the
user starts copying larger cell ranges into the sheet I have a problem.

Is it possible to keep this value of my boolean? Or to ommit changes
that are made by the
procedure I run? Or there an event that is by actions of the user
interface (but after data entry)

Thanks for your input,



Adrian

Loop with Cell Change Event
 

Typo...

application.enableevents = false
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

If your change event is going to change anything then you want to disable
events while the code is running.

Private Sub Worksheet_Change(ByVal Target As Range)
on error goto Errorhandler

application.eneableevents = false
'Sub that might or might not change the content of any cell on
Module1.Cellverification
Errorhandler:
application.enableevents = true
End Sub


--
HTH...

Jim Thomlinson


"Adrian" wrote:

Hello,


I'm new at using VBA and I'm trying to enhance the input of an excell
sheet on an automated basis, but I run into some following problem.
Checking the input and should be triggert after the user does
datainput, and this on an automated basis. So I was using a
Worksheet_Change event. But since the Checking procedure might
overwrite (certain cells of my input) I get into a loop, cause of a new
Cell Change event.

I Tried to solve this by using a boolean that is true when checking and
after check is set on No again, but it doesn't seem to work. (By
following this boolean I have seen that the program kind of looses the
value when the Worksheet_Change event is (re)started.

My Code looks like this :

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

Dim Processing As Boolean

Private Sub Workbook_Open()

Processing = False

End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)

If Processing = False Then
Processing = True

'Sub that might or might not change the content of any cell on
this sheet
Module1.Cellverification

Processing = False
End If
End Sub


________________________________________________

For simple data entry, the extra loop isn't a problem, but once if the
user starts copying larger cell ranges into the sheet I have a problem.

Is it possible to keep this value of my boolean? Or to ommit changes
that are made by the
procedure I run? Or there an event that is by actions of the user
interface (but after data entry)

Thanks for your input,



Adrian

Loop with Cell Change Event
 

Thank you very much for the info,




Jim Thomlinson wrote:
Typo...

application.enableevents = false
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

If your change event is going to change anything then you want to disable
events while the code is running.

Private Sub Worksheet_Change(ByVal Target As Range)
on error goto Errorhandler

application.eneableevents = false
'Sub that might or might not change the content of any cell on
Module1.Cellverification
Errorhandler:
application.enableevents = true
End Sub


--
HTH...

Jim Thomlinson


"Adrian" wrote:

Hello,


I'm new at using VBA and I'm trying to enhance the input of an excell
sheet on an automated basis, but I run into some following problem.
Checking the input and should be triggert after the user does
datainput, and this on an automated basis. So I was using a
Worksheet_Change event. But since the Checking procedure might
overwrite (certain cells of my input) I get into a loop, cause of a new
Cell Change event.

I Tried to solve this by using a boolean that is true when checking and
after check is set on No again, but it doesn't seem to work. (By
following this boolean I have seen that the program kind of looses the
value when the Worksheet_Change event is (re)started.

My Code looks like this :

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

Dim Processing As Boolean

Private Sub Workbook_Open()

Processing = False

End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)

If Processing = False Then
Processing = True

'Sub that might or might not change the content of any cell on
this sheet
Module1.Cellverification

Processing = False
End If
End Sub


________________________________________________

For simple data entry, the extra loop isn't a problem, but once if the
user starts copying larger cell ranges into the sheet I have a problem.

Is it possible to keep this value of my boolean? Or to ommit changes
that are made by the
procedure I run? Or there an event that is by actions of the user
interface (but after data entry)

Thanks for your input,



Adrian