| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ScreenUpdating not working
Excel XP, Win XP
The code below is an abbreviation of my code just to illustrate my problem. The code simply opens the "One.xls" file, copies from "One.xls" and PasteSpecial xlPasteValues to file "Two.xls", closes the "One.xls" file, and activates the "Two.xls" file. The code works fine. No problem there. The problem is that the screen jumps around during the Copy/Paste process even though I have ScreenUpdating set to False. Am I missing something with ScreenUpdating? Thanks for your time. Otto Application.ScreenUpdating = False Workbooks.Open Filename:=.."One.xls" With Workbooks("Two.xls").Sheets("TheSht") Cells.Copy .Range("A1").PasteSpecial xlPasteValues End With Application.DisplayAlerts = False ActiveWorkbook.Close SaveChanges:=False Application.DisplayAlerts = True Windows("Two.xls").Activate Sheets("TheSht").Activate Application.ScreenUpdating = True |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ScreenUpdating not working
There are some functions (mostly in the ATP, IIRC) that can turn the
screenupdating on. Since you're opening a workbook, maybe there's something in there that toggles the setting. I'd add a few lines like: Debug.print "Before Open--" & application.screenupdating Workbooks.Open Filename:="One.xls" Debug.print "after Open--" & application.screenupdating ..... To determine what's turning it back on. I think the best you can do is turn it back off as soon as you notice it. By the way, I think your code would document itself better if you used: activesheet.Cells.Copy or even used a worksheet variable to represent that activesheet on that newly opened workbook. (I thought that you left off a dot on my initial reading.) Otto Moehrbach wrote: Excel XP, Win XP The code below is an abbreviation of my code just to illustrate my problem. The code simply opens the "One.xls" file, copies from "One.xls" and PasteSpecial xlPasteValues to file "Two.xls", closes the "One.xls" file, and activates the "Two.xls" file. The code works fine. No problem there. The problem is that the screen jumps around during the Copy/Paste process even though I have ScreenUpdating set to False. Am I missing something with ScreenUpdating? Thanks for your time. Otto Application.ScreenUpdating = False Workbooks.Open Filename:=.."One.xls" With Workbooks("Two.xls").Sheets("TheSht") Cells.Copy .Range("A1").PasteSpecial xlPasteValues End With Application.DisplayAlerts = False ActiveWorkbook.Close SaveChanges:=False Application.DisplayAlerts = True Windows("Two.xls").Activate Sheets("TheSht").Activate Application.ScreenUpdating = True -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ScreenUpdating not working
Dave
I appreciate that tip on documenting the code a bit better. I have found it a bit challenging to figure out code I had written just a few months ago. I'll use your idea on chasing down the ScreenUpdating problem. Thanks again. Otto "Dave Peterson" wrote in message ... There are some functions (mostly in the ATP, IIRC) that can turn the screenupdating on. Since you're opening a workbook, maybe there's something in there that toggles the setting. I'd add a few lines like: Debug.print "Before Open--" & application.screenupdating Workbooks.Open Filename:="One.xls" Debug.print "after Open--" & application.screenupdating .... To determine what's turning it back on. I think the best you can do is turn it back off as soon as you notice it. By the way, I think your code would document itself better if you used: activesheet.Cells.Copy or even used a worksheet variable to represent that activesheet on that newly opened workbook. (I thought that you left off a dot on my initial reading.) Otto Moehrbach wrote: Excel XP, Win XP The code below is an abbreviation of my code just to illustrate my problem. The code simply opens the "One.xls" file, copies from "One.xls" and PasteSpecial xlPasteValues to file "Two.xls", closes the "One.xls" file, and activates the "Two.xls" file. The code works fine. No problem there. The problem is that the screen jumps around during the Copy/Paste process even though I have ScreenUpdating set to False. Am I missing something with ScreenUpdating? Thanks for your time. Otto Application.ScreenUpdating = False Workbooks.Open Filename:=.."One.xls" With Workbooks("Two.xls").Sheets("TheSht") Cells.Copy .Range("A1").PasteSpecial xlPasteValues End With Application.DisplayAlerts = False ActiveWorkbook.Close SaveChanges:=False Application.DisplayAlerts = True Windows("Two.xls").Activate Sheets("TheSht").Activate Application.ScreenUpdating = True -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ScreenUpdating not working
Dave
Here is what I found. It baffles me. The code is like: Application.ScreenUpdating = False If Dir(ThePath & "File Name") = "" Then MsgBox .......... Exit Sub Else MsgBox "Before ClearContents--" & Application.ScreenUpdating Sheets("Imported Data").Cells.ClearContents MsgBox "After ClearContents--" & Application.ScreenUpdating ------ ------ End If The "Before" MsgBox is False. The "After" MsgBox is True. Does that make sense to you? Thanks for your help. Otto "Dave Peterson" wrote in message ... There are some functions (mostly in the ATP, IIRC) that can turn the screenupdating on. Since you're opening a workbook, maybe there's something in there that toggles the setting. I'd add a few lines like: Debug.print "Before Open--" & application.screenupdating Workbooks.Open Filename:="One.xls" Debug.print "after Open--" & application.screenupdating .... To determine what's turning it back on. I think the best you can do is turn it back off as soon as you notice it. By the way, I think your code would document itself better if you used: activesheet.Cells.Copy or even used a worksheet variable to represent that activesheet on that newly opened workbook. (I thought that you left off a dot on my initial reading.) Otto Moehrbach wrote: Excel XP, Win XP The code below is an abbreviation of my code just to illustrate my problem. The code simply opens the "One.xls" file, copies from "One.xls" and PasteSpecial xlPasteValues to file "Two.xls", closes the "One.xls" file, and activates the "Two.xls" file. The code works fine. No problem there. The problem is that the screen jumps around during the Copy/Paste process even though I have ScreenUpdating set to False. Am I missing something with ScreenUpdating? Thanks for your time. Otto Application.ScreenUpdating = False Workbooks.Open Filename:=.."One.xls" With Workbooks("Two.xls").Sheets("TheSht") Cells.Copy .Range("A1").PasteSpecial xlPasteValues End With Application.DisplayAlerts = False ActiveWorkbook.Close SaveChanges:=False Application.DisplayAlerts = True Windows("Two.xls").Activate Sheets("TheSht").Activate Application.ScreenUpdating = True -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ScreenUpdating not working
Maybe turning off events before you clearcontents would prevent any
worksheet_change event from firing????? Then turn them back on: MsgBox "Before ClearContents--" & Application.ScreenUpdating application.enableevents = false Sheets("Imported Data").Cells.ClearContents application.enableevents = true MsgBox "After ClearContents--" & Application.ScreenUpdating Otto Moehrbach wrote: Dave Here is what I found. It baffles me. The code is like: Application.ScreenUpdating = False If Dir(ThePath & "File Name") = "" Then MsgBox .......... Exit Sub Else MsgBox "Before ClearContents--" & Application.ScreenUpdating Sheets("Imported Data").Cells.ClearContents MsgBox "After ClearContents--" & Application.ScreenUpdating ------ ------ End If The "Before" MsgBox is False. The "After" MsgBox is True. Does that make sense to you? Thanks for your help. Otto "Dave Peterson" wrote in message ... There are some functions (mostly in the ATP, IIRC) that can turn the screenupdating on. Since you're opening a workbook, maybe there's something in there that toggles the setting. I'd add a few lines like: Debug.print "Before Open--" & application.screenupdating Workbooks.Open Filename:="One.xls" Debug.print "after Open--" & application.screenupdating .... To determine what's turning it back on. I think the best you can do is turn it back off as soon as you notice it. By the way, I think your code would document itself better if you used: activesheet.Cells.Copy or even used a worksheet variable to represent that activesheet on that newly opened workbook. (I thought that you left off a dot on my initial reading.) Otto Moehrbach wrote: Excel XP, Win XP The code below is an abbreviation of my code just to illustrate my problem. The code simply opens the "One.xls" file, copies from "One.xls" and PasteSpecial xlPasteValues to file "Two.xls", closes the "One.xls" file, and activates the "Two.xls" file. The code works fine. No problem there. The problem is that the screen jumps around during the Copy/Paste process even though I have ScreenUpdating set to False. Am I missing something with ScreenUpdating? Thanks for your time. Otto Application.ScreenUpdating = False Workbooks.Open Filename:=.."One.xls" With Workbooks("Two.xls").Sheets("TheSht") Cells.Copy .Range("A1").PasteSpecial xlPasteValues End With Application.DisplayAlerts = False ActiveWorkbook.Close SaveChanges:=False Application.DisplayAlerts = True Windows("Two.xls").Activate Sheets("TheSht").Activate Application.ScreenUpdating = True -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ScreenUpdating not working
Dave
It amazes me how many good ideas you have. I think you have seen Excel before <g. Thanks for that. Otto "Dave Peterson" wrote in message ... Maybe turning off events before you clearcontents would prevent any worksheet_change event from firing????? Then turn them back on: MsgBox "Before ClearContents--" & Application.ScreenUpdating application.enableevents = false Sheets("Imported Data").Cells.ClearContents application.enableevents = true MsgBox "After ClearContents--" & Application.ScreenUpdating Otto Moehrbach wrote: Dave Here is what I found. It baffles me. The code is like: Application.ScreenUpdating = False If Dir(ThePath & "File Name") = "" Then MsgBox .......... Exit Sub Else MsgBox "Before ClearContents--" & Application.ScreenUpdating Sheets("Imported Data").Cells.ClearContents MsgBox "After ClearContents--" & Application.ScreenUpdating ------ ------ End If The "Before" MsgBox is False. The "After" MsgBox is True. Does that make sense to you? Thanks for your help. Otto "Dave Peterson" wrote in message ... There are some functions (mostly in the ATP, IIRC) that can turn the screenupdating on. Since you're opening a workbook, maybe there's something in there that toggles the setting. I'd add a few lines like: Debug.print "Before Open--" & application.screenupdating Workbooks.Open Filename:="One.xls" Debug.print "after Open--" & application.screenupdating .... To determine what's turning it back on. I think the best you can do is turn it back off as soon as you notice it. By the way, I think your code would document itself better if you used: activesheet.Cells.Copy or even used a worksheet variable to represent that activesheet on that newly opened workbook. (I thought that you left off a dot on my initial reading.) Otto Moehrbach wrote: Excel XP, Win XP The code below is an abbreviation of my code just to illustrate my problem. The code simply opens the "One.xls" file, copies from "One.xls" and PasteSpecial xlPasteValues to file "Two.xls", closes the "One.xls" file, and activates the "Two.xls" file. The code works fine. No problem there. The problem is that the screen jumps around during the Copy/Paste process even though I have ScreenUpdating set to False. Am I missing something with ScreenUpdating? Thanks for your time. Otto Application.ScreenUpdating = False Workbooks.Open Filename:=.."One.xls" With Workbooks("Two.xls").Sheets("TheSht") Cells.Copy .Range("A1").PasteSpecial xlPasteValues End With Application.DisplayAlerts = False ActiveWorkbook.Close SaveChanges:=False Application.DisplayAlerts = True Windows("Two.xls").Activate Sheets("TheSht").Activate Application.ScreenUpdating = True -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ScreenUpdating not working
If it worked, you may have fixed the problem with the screen updating, but you
may have caused other stuff to go wrong--maybe that event was important??????? I'd double check--just in case. Otto Moehrbach wrote: Dave It amazes me how many good ideas you have. I think you have seen Excel before <g. Thanks for that. Otto "Dave Peterson" wrote in message ... Maybe turning off events before you clearcontents would prevent any worksheet_change event from firing????? Then turn them back on: MsgBox "Before ClearContents--" & Application.ScreenUpdating application.enableevents = false Sheets("Imported Data").Cells.ClearContents application.enableevents = true MsgBox "After ClearContents--" & Application.ScreenUpdating Otto Moehrbach wrote: Dave Here is what I found. It baffles me. The code is like: Application.ScreenUpdating = False If Dir(ThePath & "File Name") = "" Then MsgBox .......... Exit Sub Else MsgBox "Before ClearContents--" & Application.ScreenUpdating Sheets("Imported Data").Cells.ClearContents MsgBox "After ClearContents--" & Application.ScreenUpdating ------ ------ End If The "Before" MsgBox is False. The "After" MsgBox is True. Does that make sense to you? Thanks for your help. Otto "Dave Peterson" wrote in message ... There are some functions (mostly in the ATP, IIRC) that can turn the screenupdating on. Since you're opening a workbook, maybe there's something in there that toggles the setting. I'd add a few lines like: Debug.print "Before Open--" & application.screenupdating Workbooks.Open Filename:="One.xls" Debug.print "after Open--" & application.screenupdating .... To determine what's turning it back on. I think the best you can do is turn it back off as soon as you notice it. By the way, I think your code would document itself better if you used: activesheet.Cells.Copy or even used a worksheet variable to represent that activesheet on that newly opened workbook. (I thought that you left off a dot on my initial reading.) Otto Moehrbach wrote: Excel XP, Win XP The code below is an abbreviation of my code just to illustrate my problem. The code simply opens the "One.xls" file, copies from "One.xls" and PasteSpecial xlPasteValues to file "Two.xls", closes the "One.xls" file, and activates the "Two.xls" file. The code works fine. No problem there. The problem is that the screen jumps around during the Copy/Paste process even though I have ScreenUpdating set to False. Am I missing something with ScreenUpdating? Thanks for your time. Otto Application.ScreenUpdating = False Workbooks.Open Filename:=.."One.xls" With Workbooks("Two.xls").Sheets("TheSht") Cells.Copy .Range("A1").PasteSpecial xlPasteValues End With Application.DisplayAlerts = False ActiveWorkbook.Close SaveChanges:=False Application.DisplayAlerts = True Windows("Two.xls").Activate Sheets("TheSht").Activate Application.ScreenUpdating = True -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ScreenUpdating = False not working | Excel Programming | |||
| ScreenUpdating = False not working | Excel Programming | |||
| ScreenUpdating function not working? | Excel Programming | |||
| screenupdating = false not working in Workbook_open sub | Excel Programming | |||
| Application.screenupdating is not working in Excel 2002 though it. | Excel Programming | |||
Linear Mode
