Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event causes program to freeze
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event causes program to freeze
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 -- Regards, Tom Ogilvy "KobusD" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event causes program to freeze
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event causes program to freeze
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event causes program to freeze
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 "KobusD" wrote in message oups.com... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Event change freeze my workbook | Excel Programming | |||
event management program | Excel Discussion (Misc queries) | |||
Change event and calculate event | Excel Programming | |||
VBA password / Program Freeze | Excel Programming | |||
VBA Password / Program freeze | Excel Programming |