Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
'Application.ScreenUpdating = False' isn't working
This code just copies values from the active ws to another wb and then
pastes values from the wb back to the active ws. It works fine but I want to hide the wb and stop the screen updating until the code is complete... How can I do this? Sub Populate_H6() Dim ws As Worksheet Dim wb As Workbook Dim rng As Range Dim rCell As Range Application.ScreenUpdating = False Set ws = ActiveSheet Set wb = Workbooks.Open("A:\Lookup.xls") With ws Set rng = .Range(.Cells(1, 1), _ .Cells(.Rows.Count, 2).End(xlUp)) End With For Each rCell In rng.Cells Select Case rCell.Value Case "TiM" rCell.Offset(0, 1).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 1) rCell.Offset(0, 2).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 2) wb.Worksheets(13).Range(rCell.Address) _ .Offset(3, 5).Copy ws.Range(rCell.Address).Offset(0, 3) Case "MiM" rCell.Offset(0, 1).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 7) rCell.Offset(0, 2).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 8) wb.Worksheets(13).Range(rCell.Address) _ .Offset(3, 11).Copy ws.Range(rCell.Address).Offset(0, 3) Case "WiM" rCell.Offset(0, 1).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 13) rCell.Offset(0, 2).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 14) wb.Worksheets(13).Range(rCell.Address) _ .Offset(3, 17).Copy ws.Range(rCell.Address).Offset(0, 3) End Select Next With ws ws.Columns("D").ClearFormats ws.Columns("D").AutoFit End With wb.Close savechanges:=False Application.ScreenUpdating = True End Sub Thank You, -- Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
'Application.ScreenUpdating = False' isn't working
It looks like you have the .screenupdating lines in the appropriate spots.
Any chance that you have some event (workbook_Open in lookup.xls???) that turns the screenupdating back on? If yes, you could turn off screenupdating right after you open lookup.xls--or you could disable events before you open that file: application.enableevents = false set wb = workbooks.open(.... application.enableevents = true You could have events that fire when you're making a selection change or a worksheet change, too. You may want to spend some time isolating where the problem occurs. By the way, I wouldn't open a file that's on my floppy. I'd copy it to my harddrive and open it from there. "Dan R." wrote: This code just copies values from the active ws to another wb and then pastes values from the wb back to the active ws. It works fine but I want to hide the wb and stop the screen updating until the code is complete... How can I do this? Sub Populate_H6() Dim ws As Worksheet Dim wb As Workbook Dim rng As Range Dim rCell As Range Application.ScreenUpdating = False Set ws = ActiveSheet Set wb = Workbooks.Open("A:\Lookup.xls") With ws Set rng = .Range(.Cells(1, 1), _ .Cells(.Rows.Count, 2).End(xlUp)) End With For Each rCell In rng.Cells Select Case rCell.Value Case "TiM" rCell.Offset(0, 1).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 1) rCell.Offset(0, 2).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 2) wb.Worksheets(13).Range(rCell.Address) _ .Offset(3, 5).Copy ws.Range(rCell.Address).Offset(0, 3) Case "MiM" rCell.Offset(0, 1).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 7) rCell.Offset(0, 2).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 8) wb.Worksheets(13).Range(rCell.Address) _ .Offset(3, 11).Copy ws.Range(rCell.Address).Offset(0, 3) Case "WiM" rCell.Offset(0, 1).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 13) rCell.Offset(0, 2).Copy wb.Worksheets(13) _ .Range(rCell.Address).Offset(3, 14) wb.Worksheets(13).Range(rCell.Address) _ .Offset(3, 17).Copy ws.Range(rCell.Address).Offset(0, 3) End Select Next With ws ws.Columns("D").ClearFormats ws.Columns("D").AutoFit End With wb.Close savechanges:=False Application.ScreenUpdating = True End Sub Thank You, -- Dan -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
'Application.ScreenUpdating = False' isn't working
Oh I know, I mapped a network drive on A: for some odd reason. But yes
you're right I do have a worksheet_change event on the Lookup sheet. This is probably a stupid question but if I turn off screenupdating after I open Lookup then it's still going to show it open it correct? Or do I have to turn it off both before and after I open Lookup? Thanks Dave, -- Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
'Application.ScreenUpdating = False' isn't working
nevermind, you're exactly right... that worked. I hate to push my luck
but could you please give me some insight as to how to display as progress bar while the code is running? Thanks, -- Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
'Application.ScreenUpdating = False' isn't working
That first section of code is pretty small and doesn't do any screenupdating
anyway. You're not gaining too much by turning off screenupdating before the ..open line. And if you turn off screenupdating after you open lookup.xls, then it'll show up. But you could activate the other workbook right after you open the file, then turn off screenupdating--or turn off the events. Set ws = ActiveSheet Set wb = Workbooks.Open("A:\Lookup.xls") ws.parent.activate Application.ScreenUpdating = False With ws Set rng = .Range(.Cells(1, 1), _ .Cells(.Rows.Count, 2).End(xlUp)) End With ===== might be ok. "Dan R." wrote: Oh I know, I mapped a network drive on A: for some odd reason. But yes you're right I do have a worksheet_change event on the Lookup sheet. This is probably a stupid question but if I turn off screenupdating after I open Lookup then it's still going to show it open it correct? Or do I have to turn it off both before and after I open Lookup? Thanks Dave, -- Dan -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
'Application.ScreenUpdating = False' isn't working
I'd use the statusbar at the bottom.
Application.statusbar = "Processing: " & mycell.row & " at: " & now Inside the loop then at the end, give the statusbar back to excel: application.statusbar = false ===== But if you want a statusbar, you can look at John Walkenbach's site: http://www.j-walk.com/ss/excel/tips/tip34.htm "Dan R." wrote: nevermind, you're exactly right... that worked. I hate to push my luck but could you please give me some insight as to how to display as progress bar while the code is running? Thanks, -- Dan -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
'Application.ScreenUpdating = False' isn't working
Works great Dave.
Thank You, -- Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set Application.ScreenUpdating = False for Gen use | Excel Discussion (Misc queries) | |||
Using the Application.ScreenUpdating = False? | Excel Programming | |||
Using the Application.ScreenUpdating = False? | Excel Programming | |||
Application.screenUpdating = False | Excel Programming | |||
Application.ScreenUpdating = False | Excel Programming |