How To Keep Worksheet_Change From Firing?
 

In MS Access VBA code, I'm creating a spreadsheet and pushing some
WorkSheet_Change code into it.

But as I populate various cells - after adding the code - the event
keeps firing and I'm tripping over chicken-egg situations.

I guess the obvious is not to add the code until everything else is
done.. but I'm wary of yet another chicken-and-egg conundrum.

Is there any way to prevent event code from firing until I've finished
building the spreadsheet?

How To Keep Worksheet_Change From Firing?
 

Use the line
Application.enableevents = false
to tuirn off events and
Application.enableevents = false
To turn them back on again. It is VERY importtant that you remeber to turn
them back on as this is a persistent application level setting. If you forget
to turn them bakc on then then no events will fire in Excel (until you run
code to turn the events back on).
--
HTH...

Jim Thomlinson


"PeteCresswell" wrote:

In MS Access VBA code, I'm creating a spreadsheet and pushing some
WorkSheet_Change code into it.

But as I populate various cells - after adding the code - the event
keeps firing and I'm tripping over chicken-egg situations.

I guess the obvious is not to add the code until everything else is
done.. but I'm wary of yet another chicken-and-egg conundrum.

Is there any way to prevent event code from firing until I've finished
building the spreadsheet?

How To Keep Worksheet_Change From Firing?
 

Try this:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Exit Sub
'Your code here
End Sub

Mike

"PeteCresswell" wrote:

In MS Access VBA code, I'm creating a spreadsheet and pushing some
WorkSheet_Change code into it.

But as I populate various cells - after adding the code - the event
keeps firing and I'm tripping over chicken-egg situations.

I guess the obvious is not to add the code until everything else is
done.. but I'm wary of yet another chicken-and-egg conundrum.

Is there any way to prevent event code from firing until I've finished
building the spreadsheet?

How To Keep Worksheet_Change From Firing?
 

Application.enableevents = true OOPs.
To turn them back on again.
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Use the line
Application.enableevents = false
to tuirn off events and
Application.enableevents = false
To turn them back on again. It is VERY importtant that you remeber to turn
them back on as this is a persistent application level setting. If you forget
to turn them bakc on then then no events will fire in Excel (until you run
code to turn the events back on).
--
HTH...

Jim Thomlinson


"PeteCresswell" wrote:

In MS Access VBA code, I'm creating a spreadsheet and pushing some
WorkSheet_Change code into it.

But as I populate various cells - after adding the code - the event
keeps firing and I'm tripping over chicken-egg situations.

I guess the obvious is not to add the code until everything else is
done.. but I'm wary of yet another chicken-and-egg conundrum.

Is there any way to prevent event code from firing until I've finished
building the spreadsheet?

How To Keep Worksheet_Change From Firing?
 

Per Jim Thomlinson:
To turn them back on again. It is VERY importtant that you remeber to turn
them back on as this is a persistent application level setting. If you forget
to turn them bakc on then then no events will fire in Excel (until you run
code to turn the events back on).

But only in the context of the currently-running instance of
Excel, right?

i.e. even if the code fails to turn it back on, once they close
the spreadsheet, exit from Excel, and then open some other sheet
they'll be ok...
--
PeteCresswell