Change event causes program to freeze

[KobusD]

When I insert rows on this sheet the program freezes. I managed to fix
the Copy & Paste with a solution provided by Tom Ogilvy in this NG. I
am a bit stuck with the inserting problem. Hope someone can help me:


Private Sub Worksheet_Change(ByVal Target As Range)


If Application.CutCopyMode > 0 Then Exit Sub


'Turn calculation and screen updating off whilst the following code
executes
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


'Unprotect the worksheet and disable the selection of locked cells
Worksheets("Output").Unprotect Password:="xxxxx"
Worksheets("Output(budgets)").Unprotect Password:="xxxxxx"


Worksheets("Budgets").CompileButt.BackColor = RGB(255, 0, 0)
Worksheets("Budgets").CompileButt.Caption = "Needs a Compile!"
Worksheets("Budgets").CompileButt.PrintObject = True


Worksheets("Output").Range("A2").Value = "A compile is outstanding!"
Worksheets("Output(budgets)").Range("F2").Value = "A compile is
outstanding!"


'Protect the worksheet and disable the selection of locked cells
Worksheets("Output").Protect Password:="xxxxx"
Worksheets("Output").EnableSelection = xlUnlockedCells
Worksheets("Output(budgets)").Protect Password:="xxxxxx"
Worksheets("Output(budgets)").EnableSelection = xlUnlockedCells

End Sub

 

[Tom Ogilvy]

Possibly disable events while you do your deed.

Private Sub Worksheet_Change(ByVal Target As Range)


If Application.CutCopyMode > 0 Then Exit Sub

On Error goto ErrHandler
'Turn calculation and screen updating off whilst the following code executes
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

'Unprotect the worksheet and disable the selection of locked cells
Worksheets("Output").Unprotect Password:="xxxxx"
Worksheets("Output(budgets)").Unprotect Password:="xxxxxx"


Worksheets("Budgets").CompileButt.BackColor = RGB(255, 0, 0)
Worksheets("Budgets").CompileButt.Caption = "Needs a Compile!"
Worksheets("Budgets").CompileButt.PrintObject = True


Worksheets("Output").Range("A2").Value = "A compile is outstanding!"
Worksheets("Output(budgets)").Range("F2").Value = "A compile is
outstanding!"


'Protect the worksheet and disable the selection of locked cells
Worksheets("Output").Protect Password:="xxxxx"
Worksheets("Output").EnableSelection = xlUnlockedCells
Worksheets("Output(budgets)").Protect Password:="xxxxxx"
Worksheets("Output(budgets)").EnableSelection = xlUnlockedCells

ErrHandler:
Application.EnableEvents = True
End Sub

 

[KobusD]

Thanks Tom. It still does not work. I inserted a "Toggle Breakpoint"
to see what actually happens when the event is triggered. It seems to
go through the SUB without picking up any errors, but at the end of the
Sub I cannot go back to Excel since it has frozen.

 

[KobusD]

Hi, Tom

I've realised that it is the "screen updating" command that is causing
the problem. With this commented out the inserting & deleting of rows
are OK! The screen graphics are however a bit messy and I need to
perform something like "ScreenUpdating = False". Any ideas?

 

[Tom Ogilvy]

I'm incredulous about ScreenUpdating being the problem, but nontheless, turn
it off at the top and
just turn it back on at the end:

Private Sub Worksheet_Change(ByVal Target As Range)


If Application.CutCopyMode > 0 Then Exit Sub


'Turn calculation and screen updating off whilst the following code
executes
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


'Unprotect the worksheet and disable the selection of locked cells
Worksheets("Output").Unprotect Password:="xxxxx"
Worksheets("Output(budgets)").Unprotect Password:="xxxxxx"


Worksheets("Budgets").CompileButt.BackColor = RGB(255, 0, 0)
Worksheets("Budgets").CompileButt.Caption = "Needs a Compile!"
Worksheets("Budgets").CompileButt.PrintObject = True


Worksheets("Output").Range("A2").Value = "A compile is outstanding!"
Worksheets("Output(budgets)").Range("F2").Value = "A compile is
outstanding!"


'Protect the worksheet and disable the selection of locked cells
Worksheets("Output").Protect Password:="xxxxx"
Worksheets("Output").EnableSelection = xlUnlockedCells
Worksheets("Output(budgets)").Protect Password:="xxxxxx"
Worksheets("Output(budgets)").EnableSelection = xlUnlockedCells
Application.ScreenUpdating = True

End Sub