Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro not firing off | Excel Programming | |||
Worksheet_Change and Workbook_SheetChange not firing | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |