Loop with Cell Change Event

[Adrian]

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

[Jim Thomlinson]

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
>
>

[Jim Thomlinson]

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
> >
> >

[Adrian]

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
> > >
> > >