when running the macro screen goes blank

A

ap1

New Member
Joined
Mar 11, 2002
Messages
26
I am running the excel macro and screen goes blank and hour glass is still there how to get rid of the screen going blank if at all it is possible.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
On 2002-03-13 12:29, ap1 wrote:
I am running the excel macro and screen goes blank and hour glass is still there how to get rid of the screen going blank if at all it is possible.
Click to expand...

Can you put up the code?

Does it have:

application.screenUpdating = false

in the macro?
 
Upvote 0
ChDir "C:Data"
Workbooks.OpenText Filename:="C:DataEpdbBillAddress", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1))
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "EPDB PIN"
Range("B1").Select
ActiveCell.FormulaR1C1 = ""
Range("A1").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = "EPDB PIN"
Range("E1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Unit #"
Range("F1").Select
Columns("E:E").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("D:D").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("F1").Select
ActiveCell.FormulaR1C1 = "EPDB Provider Name"
Range("F2").Select
Columns("F:F").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=4
Range("G1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr #"
Range("G2").Select
Columns("G:G").EntireColumn.AutoFit
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("H1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Bldg NM"
Range("H2").Select
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Range("I1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Line 1"
Range("J1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Line 2"
Range("J2").Select
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Range("K1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr City"
Range("L1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr State"
Range("M1").Select
Columns("K:K").EntireColumn.AutoFit
Columns("L:L").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Zip"
Range("N1").Select
Columns("M:M").EntireColumn.AutoFit
Columns("M:M").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
ActiveWindow.SmallScroll ToRight:=-3
ActiveWindow.LargeScroll ToRight:=-1
Range("D1").Select
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.LargeScroll ToRight:=2
Range("D1:M1").Select
Selection.Font.Bold = True
Columns("E:E").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveWindow.SmallScroll ToRight:=8
ActiveWindow.ScrollColumn = 1
Cells.Select
ActiveWindow.SmallScroll ToRight:=6
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.ScrollColumn = 8
Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Key2:=Range("J2") _
, Order2:=xlAscending, Key3:=Range("I2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Range("D3").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
Range("D3:M3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Columns("A:C").Select
Range("C1").Activate
Selection.EntireColumn.Hidden = True
Cells.Select
Range("D1").Activate
ActiveSheet.PageSetup.PrintArea = Selection.Address
Range("D1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 160
End With
ActiveWindow.LargeScroll Down:=0
Columns("F:F").ColumnWidth = 75.71
Columns("F:F").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("F:F").ColumnWidth = 38.57
ActiveWindow.LargeScroll Down:=23
Range("I791").Select
ActiveWindow.LargeScroll Down:=-23
ActiveWindow.SmallScroll ToRight:=6
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
ActiveWindow.LargeScroll ToRight:=-3
Columns("E:E").EntireColumn.AutoFit
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 160
End With
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 180
End With
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 180
End With
Columns("H:H").ColumnWidth = 28.29
ActiveWindow.SmallScroll ToRight:=2
Columns("I:I").ColumnWidth = 27.71
ActiveWindow.SmallScroll ToRight:=1
Columns("J:J").ColumnWidth = 24.86
ActiveWindow.SmallScroll ToRight:=2
ActiveWindow.LargeScroll ToRight:=-2
Range("D5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("D1").Select
ActiveWindow.LargeScroll ToRight:=1
Range("D1:M4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D6").Select
Range("D1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
ActiveWindow.SmallScroll ToRight:=1
Range("E5").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 180
End With
Columns("G:G").ColumnWidth = 17.57
ActiveWindow.SmallScroll ToRight:=-2
Range("G4").Select
Selection.EntireRow.Insert
Range("G5").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Address"
Range("G6").Select
ActiveCell.FormulaR1C1 = "Number"
Range("G5:M5").Select
ActiveWindow.LargeScroll ToRight:=-2
Range("G5").Select
Selection.ClearContents
Rows("5:5").Select
Range("D5").Activate
Selection.Delete Shift:=xlUp
Range("G5").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "EPDB Billing Addr #"
Range("H9").Select
Columns("G:G").ColumnWidth = 18.29
Columns("G:G").ColumnWidth = 18
Range("D3:M3").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE("" Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
Range("D4").Select

End Sub
 
Upvote 0
On 2002-03-13 12:46, ap1 wrote:
Thanks it works great with Application.ScreenUpdating = False
Click to expand...

Don't forget to put Application.ScreenUpdating = True

at the end
 
Upvote 0
At the end of the macro control is returned to Excel. This automatically sets screenupdating back to true.

The only time I've had problems with screenupdating is when I've set it to false before displaying a userform.

Create a UserForm1 and try this for an example:<pre>
Public Sub main()

Application.ScreenUpdating = False
UserForm1.Show

End Sub</pre>

Now drag the useform all over the screen.

_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-03-13 13:38
 
Upvote 0
You must log in or register to reply here.

Similar threads

The Ruff Report
  • Question
Advice to Improve Email List Macro
Replies
1
Views
120
FormR
FormR
zookeepertx
  • Question
Weird Excel quirks driving me crazy! Can anyone explain?
Replies
1
Views
86
Jeffrey Mahoney
Jeffrey Mahoney
J
  • Question
Using Screen Updating
2
Replies
10
Views
446
julhs
J
Dan Wilson
  • Solved
Question
Replies
3
Views
196
Peter_SSs
Peter_SSs
J
  • Question
Is there a way to have several small spreadsheets open on a screen at the same time in excel?
Replies
1
Views
131
Logit
Logit

Forum statistics

Threads
1,214,636
Messages
6,120,668
Members
448,977
Latest member
moonlight6

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top