Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & Paste Freezing Excel
I have a workbook with 50+ sheets in it. All but 2 of the sheets are
identical. In all of these identical sheets an employee # will be entered and some formatting and formulas are added via code. Since most of the sheets are identical, I put the code in the Workbook_SheetChange. The code works fine but the problem is when I try to Copy & Paste on any of the sheets, Excel freezes and I can only exit Excel from the task bar in Windows. I am prompted to save changes and am able to do so. Below is the code I am using. Is there some obvious reason why this is happening? Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Application.ScreenUpdating = False If Not Intersect(Target, Range("A7:A100")) Is Nothing Then ActiveCell.Offset(-1, 0).Select ActiveCell.Range("A1:K1").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone ....rest of code Thanks, Phil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & Paste Freezing Excel
In the code you posted, where is the Copy & Paste code? All you are doing is remove border line. Anyway, so the problem occurs when you Copy & Paste, right? When you Copy and Paste the sheet, again the event Workbook_SheetChange is fired. So it will run again. May be this is causing the freezing. Try as under. In Thisworkbook, General Section define a public variable as under: Public notAgain As Boolean Then in the Worksheet_Change add following two lines at TOP! Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If notAgain Then Exit Sub notAgain = True 'your all code 'then following at the bottom before End Sub notAgain = False End Sub Or if at any place in worksheet_change code you are using exit sub (except for the one I told above at top) just above exit sub enter line notAgain = False Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & Paste Freezing Excel
Sharad,
The copy & paste are all being done manually. There is no copy & paste done via code. The solution you provided does not work. There is still freezing of excel when you try to paste. I thought that even though the code is in the ThisWorkbook section it would only pertain to the range I wanted to use: If Not Intersect(Target, Range("A7:A100")) Is Nothing Then Why does the code still fire if anything happens outside that range? Phil "Sharad" wrote in message ... In the code you posted, where is the Copy & Paste code? All you are doing is remove border line. Anyway, so the problem occurs when you Copy & Paste, right? When you Copy and Paste the sheet, again the event Workbook_SheetChange is fired. So it will run again. May be this is causing the freezing. Try as under. In Thisworkbook, General Section define a public variable as under: Public notAgain As Boolean Then in the Worksheet_Change add following two lines at TOP! Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If notAgain Then Exit Sub notAgain = True 'your all code 'then following at the bottom before End Sub notAgain = False End Sub Or if at any place in worksheet_change code you are using exit sub (except for the one I told above at top) just above exit sub enter line notAgain = False Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy and paste from Excel | Excel Discussion (Misc queries) | |||
Excel copy and paste | Excel Worksheet Functions | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
copy and paste from excel to ppt with VBA | Excel Discussion (Misc queries) |